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

Try to remove the Exit Sub line.

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

Accepted answer

Viorel 112.5K Reputation points

2023-10-02T14:30:59.21+00:00

Try to remove the Exit Sub line.
Please sign in to rate this answer.

1 person found this answer helpful.
Roberto 20 Reputation points

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

No that did not work :-(

Barry Schwarz 2,186 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 2,186 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 2,186 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 2,186 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.
Sign in to comment

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

0 additional answers