39 questions
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
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(256, 28.999999999999996%, 34%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ER%3C/text%3E%3C/svg%3E)
Roberto
20
Reputation points
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
Bing Bing Search APIs Bing Spell Check API
Microsoft 365 and Office Install, redeem, activate For business Windows
Microsoft 365 and Office Excel For business Windows
3,887 questions
Windows for business Windows Client for IT Pros User experience Other
Community Center Not monitored
46,175 questions
{count} votes
-
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.
Sign in to comment
Accepted answer
-
Viorel 122.5K Reputation points2023-10-02T14:30:59.21+00:00 -
Roberto 20 Reputation points
2023-10-02T14:40:24.81+00:00 No that did not work :-(
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(172.8, 53%, 26%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EBS%3C/text%3E%3C/svg%3E)
Barry Schwarz 3,746 Reputation points2023-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 SubThe 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 IfNext 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 itskips 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.
Sign in to comment -