I would like for my command to repeat through each row, at the moment it only runs on 1 row

Question

I would like for my command to repeat through each row, at the moment it only runs on 1 row

Roberto 20

I have the  current VBA
Sub Subtract()
cPart = Sheet3.Range("a2, a1000")
cQty = Sheet3.Range("b2, b1000")
'loop

lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).row
For i = 2 To lastrow
    If Sheet1.Cells(i, 1) = cPart Then
        'found it
        Sheet1.Cells(i, 4) = Sheet1.Cells(i, 4) - cQty
        Exit Sub

    End If

Next i

End Sub
This only runs for 1 row, but I would like for it to repeat through every active row.. Please help

Roberto 20 Reputation points

2023-10-02T15:38:24.0633333+00:00

This is an inventory spreadsheet that I am trying to make at work
Sheet 1 is the actual inventory

Sheet 2 is used for a email template that is sent when we are low on ink
This is working with no issues

Sheet 3 is where I want to be able to check out ink and also add ink to the inventory

For some reason the function is only accounting for the first row, but I would like for it to go down every active row on sheet 3 and update each of those items in sheet 1
Roberto 20 Reputation points

2023-10-02T20:06:06.3366667+00:00

Also on Sheet 3 the issue is that it is only accounting for row 2... so no matter what I enter row 2 works with no issues updating in the inventory sheet/Sheet 1

As mentioned though I want to be able to place multiple items and update the inventory by clicking on checkout to subtract it from the inventory all at once.

Accepted answer

0 additional answers

Your answer

Roberto 20 Reputation points

2023-10-02T15:38:24.0633333+00:00

This is an inventory spreadsheet that I am trying to make at work
Sheet 1 is the actual inventory

Sheet 2 is used for a email template that is sent when we are low on ink
This is working with no issues

Sheet 3 is where I want to be able to check out ink and also add ink to the inventory

For some reason the function is only accounting for the first row, but I would like for it to go down every active row on sheet 3 and update each of those items in sheet 1
Roberto 20 Reputation points

2023-10-02T20:06:06.3366667+00:00

Also on Sheet 3 the issue is that it is only accounting for row 2... so no matter what I enter row 2 works with no issues updating in the inventory sheet/Sheet 1

As mentioned though I want to be able to place multiple items and update the inventory by clicking on checkout to subtract it from the inventory all at once.

Answer 1

Viorel 121.8K

Try to remove the Exit Sub line.

Roberto 20 Reputation points

2023-10-02T14:40:24.81+00:00

No that did not work :-(
Barry Schwarz 3,746 Reputation points

2023-10-03T08:50:12.59+00:00

In what way did it not work? Have yu tried stepping through the code using F8 in lthe VBA editor? What is the value of lastrow when your loop ends?
Barry Schwarz 3,746 Reputation points

2023-10-03T08:53:26.0966667+00:00

In what way did it not work? Show us the new code with the Exit statement removed.

Have you tried stepping through the code using F8 in the VBA editor? What are the values of i and lastrow when your loop finishes?
Roberto 20 Reputation points

2023-10-03T13:30:06.26+00:00
Sub Subtract() cPart = Sheet3.Range("a2, a1000") cQty = Sheet3.Range("b2, b1000") 'loop lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).row For i = 2 To lastrow If Sheet1.Cells(i, 1) = cPart Then 'found it Sheet1.Cells(i, 4) = Sheet1.Cells(i, 4) - cQty End If Next i End Sub

The command still runs, but it only runs on row 2 it doesn't continue through every row on column a

So main function I want every input on sheet3 column a to be identified in sheet1 column a or c and once it is identified i want the Qty from column b sheet3 to be added or subtracted depending on what function is being done to column d sheet1
Roberto 20 Reputation points

2023-10-03T14:13:05.4766667+00:00

So the function itself still works there are no error messages and here is the code after removing exit sub
Sub Subtract()

cPart = Sheet3.Range("a2, a1000")

cQty = Sheet3.Range("b2, b1000")

'loop

lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).row

For i = 2 To lastrow

If Sheet1.Cells(i, 1) = cPart Then
'found it
Sheet1.Cells(i, 4) = Sheet1.Cells(i, 4) - cQty
End If

Next i

End Sub

I also ran F8 in VBA Editor and during the first portion it only goes through A2 and B2 and doesn't loop
Roberto 20 Reputation points

2023-10-03T15:07:56.76+00:00
Also when going through pressing f8 the first time around it will go through each row, but once it gets to Next i it does the following
returns to
If Sheet1.Cells(i, 1) = cPart Then

'found it

skips Sheet1.Cells(i, 4) = Sheet1.Cells(i, 4) - cQty

End If

Next i
and just loops from here constantly skipping Sheet1.Cells(i, 4) = Sheet1.Cells(i, 4) - cQty
Barry Schwarz 3,746 Reputation points

2023-10-03T18:14:47.6966667+00:00

What are the values of lastrow and i when the loop finishes and before the Exit Sub runs?

Yes, your assignment statements for cPart and cQty use only the first cell in the range. The second argument seems to serve no purpose. What did you want to happen? Were you expecting Cpart and cQty to be arrays of values?
Roberto 20 Reputation points

2023-10-03T19:35:06.28+00:00

So main function I want is for every input on sheet3 column a to be identified in sheet1 column a or c and once it is identified i want the Qty from column b sheet3 to be added or subtracted depending on what function is being done to column d sheet1
Barry Schwarz 3,746 Reputation points

2023-10-04T02:43:27.4066667+00:00
The reason your code appeared to work for only row 2 is cPart was assigned the value 418269. You loop processed every row in Sheet3 but row2 contain the only match.

You will need two loops, an inner one nested in an outer one.

The outer loop should iterate through the rows in Sheet3 starting in row 2 and ending in the last row that has data (similar to how you compute lastrow currently). Its only purpose is to pass the current row number to the inner loop.

The inner loop should iterate through the rows in Sheet1 starting in row2 and ending in lastrow as currently coded. It compares the two part numbers in the sheet 1 row with the part number in the sheet3 row. When they are equal, it performs the appropriate arithmetic (subtraction in your example) using the quantity values from the two rows and stores the result in the sheet1 row. Exit the loop after the match is processed. If the loop ends without finding a match (i will equal lastrow+1), you may want to issue an error message.

I don't know if it is more efficient or not but you can replace the inner loop with the Find function.

Share via

I would like for my command to repeat through each row, at the moment it only runs on 1 row

0 additional answers

Your answer