VBA Excel Range problem

[BJF]

Hi,

I know this is more an Access forum, but maybe someone can help me.

I have the following code in a basic Excel Inventory worksheet :

Private Sub Assembly_Click()
Range("C3").Value = Range("C3").Value - Range("E3").Value


End Sub

Private Sub Received_Click()
Range("C3").Value = Range("C3").Value + Range("D3").Value


End Sub

It functions fine for the 3rd row, but I would like the code to apply to every row within the C, D, and E column.

is there an easy way to modify this code to specify all rows, or at least a decent set of rows , for example C3 through C100, etc....

Thanks for any input,

Brian

 

[Alansidman]

Try this:

Private Sub Assembly_Click()
Dim i as Integer
Dim LastRow as Integer

LastRow = Range("C" & Rows.Count).End(xlup).Row

For i = 3 to LastRow
cells(i,3).FormulaR1C1 = "=" & cells(i,3) & "-" & cells(i,5)
Next
End Sub

Private Sub Received_Click()
Dim i as Integer
Dim LastRow as Integer

LastRow = Range("C" & Rows.Count).End(xlup).Row

For i = 3 to LastRow
cells(i,3).FormulaR1C1 = "=" & cells(i,3) & "+" & cells(i,4)
Next
End Sub

 

[BJF]

Thanks for the response Alan,

I tried, but still getting exact same behavior from the buttons as with the original code.

I have an inventory item in Row 3, with a Quantity in Stock (Column C), a Received (Column D) , and An Assembly (Column E)

Now If I enter a new product in Row 4, and add to column D and click the Received button, it adds on to the total in Column C/ Row 3.

I need it to put a total in Column C / Row 4!!!!

I get identical results with original code and with yours.

Still stuck, if youve got any suggestions

Thanks,
Brian:banghead:

 

[Alansidman]

I misunderstood your requirements. I assumed that you had data in column C and was updating data in Columns D and E only. Never assumed that you were adding new data in column C. Will re-look at tweaking the code. Will you be changing the data in columns D and E once entered or only entering on a new line. Some sample data with expected results would help me to help you. Click on Go Advanced and upload a copy of your workbook. Show and highlight the expected results

 

[DonkeyKong]

THE IMPORTANT STUFF IS AT THE BOTTOM -- The rest is just an instructional course to get there --

I think your problem is that you are using ranges for individual cells. You might have better luck if you used cell instead of range...

Sheet1.cells(x,y).value = sheet1.cells(x,y) + sheet1.cells(x,y)

I think your best bet is to assign a counter and tell it to add cells until it comes across a blank...
Do while Sheet1.cells(aSpot, x) <> ""

aSpot = 1

Sheet1.cells(aSpot,x).value = sheet1.cells(aSpot,y) + sheet1.cells(aSpot,z)

aSpot = aSpot + 1

Sheet1.cells(aSpot,x)

Loop

To make this actually work, I would assign names to your cells so that they are variables

tSpot = 1
QuantityA = Sheet1.Cells(tSpot, 1)
QuantityB = Sheet1.Cells(tSpot, 2)

Do while QuantityA <> ""

QuantityA + QuantityB = TotalQuantity

Sheet1.Cells(tSpot, 1).value = TotalQuantity

tSpot = tSpot + 1

Loop

Hope that helps. If you need more direction I will try to help.

 

[BJF]

Hi Alan -

Please see attached file:

There is a sample product in row 3

I'd like to have the same functionality by using the Received and Assembled buttons when inputing further products in rows 4,5,6 and so on.

Right now if I put a number in the received column in row 4 or 5 or anyother column, it keeps tallying up a total in row 3 (the row 3 product)

Thanks,
Brian

 

[DonkeyKong]

Just use a variation of what I told you. I don't feel like writing out a whole program for it, but you just have to tell it to stop doing something when the ID# changes.
totalTotal = 0
So have it add up individual cells and then do a = total,
do totalTotal = totaltotal + total
And Loop until the ID changes and then restart.

ColumnE = Total -------ColumnF------- ColumnG = TotalTotal
Do--------- 5 ----------totaltotal = 5
loop------- 10 ---------totaltotal = 15
loop------- 100000 ----totaltotal = 100015 ------- 100015
end loop, start new loop

 

[Brianwarnock]

I am not going to comment on this as an inventory system as I have never done one, but would have expected the system to have some kind of audit or tracking facility. however as I understand it you want any figure typed into column D or E to be added /subtracted from the coresponding column C.

I would not rely on the user clicking anything but would use the SheetChange event .

Brian

Private Sub Worksheet_Change(ByVal Target As Range)
Dim tr As Long
Dim tc As Long

tr = Target.Row
tc = Target.Column

If tc = 4 Then                  'column D,any cell changed
    Cells(tr, "C") = Cells(tr, "C") + Cells(tr, "D")
ElseIf tc = 5 Then
    Cells(tr, "C") = Cells(tr, "C") - Cells(tr, "E")
End If
        
End Sub

 

[Brianwarnock]

BTW Alan's code works as expected, but as it does the arithmetic on all rows then it continues to add/subtract data that has already been computed, surely not what is required.

Brian

 

[BJF]

To Brianwarnock,

Thank you so much, the code works great and does exactly what i was looking for it to do and without clicking the buttons.

Sincerely,
Brian