I have two sheets. One sheet has warehouse numbers down column A, product code in column B, and stock qty in column D. On the second sheet I am putting the warehouse number in B5, and a list of stocked items in D5. The macro below works....sometimes. If not I get an Overflow error. I have ran it line by line. The overflow error occurs at line 21. This code works if I change it from integer to long. But it takes a super long time that way. Is there a way I can speed up the long, or get the integer to stop returning an overflow error? I do have ranges if that would make a difference.
on the check_stock sheet:
warehouse number range is: B5.
product list range is: D5:D305
on the stocksheet sheet:
warehouse numbers range is: A2:A156679
product list range is: B2:B156679
stock qty range is: D2:D156679
**I did remove all items from the warehouse list that had a 0 as the quantity, so maybe it would run better if I remove the quantity column since its not relevant and try to say "if "check_stock!B5" is found in "stocksheet!A2:A156679", AND "check_stock!D5:D305" is found in "stocksheet!B2:B156679" THEN color cell (146, 208, 80), if not THEN no fill. I just cant figure out how to do that.**
I appreciate your help in advance. VBA below:
Sub checkStock()
Dim selectedWH As String
Dim checkPartCounter As Integer
Dim stockRowCounter As Integer
Dim checkSheet As Worksheet, stockSheet As Worksheet
Dim foundPart As Boolean, foundPartInStock As Boolean
Set checkSheet = Sheets("check_stock6")
Set stockSheet = Sheets("Stocksheet")
selectedWH = checkSheet.Range("B5")
checkPartCounter = 5
Do While checkSheet.Range("D" & checkPartCounter) <> ""
stockRowCounter = 2
foundPart = False
foundPartInStock = False
Do While stockSheet.Range("A" & stockRowCounter).Value <> ""
If stockSheet.Range("A" & stockRowCounter).Value = selectedWH And stockSheet.Range("B" & stockRowCounter).Value = checkSheet.Range("D" & checkPartCounter).Value Then
foundPart = True
If stockSheet.Range("D" & stockRowCounter).Value > 0 Then
foundPartInStock = True
Else
foundPartInStock = False
End If
Exit Do
End If
stockRowCounter = stockRowCounter + 1
Loop
If foundPartInStock Then
With checkSheet.Range("D" & checkPartCounter).Interior
.Color = RGB(0, 255, 0)
.Pattern = xlSolid
End With
Else
With checkSheet.Range("D" & checkPartCounter).Interior
.Pattern = xlNone
End With
End If
checkPartCounter = checkPartCounter + 1
Loop
End Sub
on the check_stock sheet:
warehouse number range is: B5.
product list range is: D5:D305
on the stocksheet sheet:
warehouse numbers range is: A2:A156679
product list range is: B2:B156679
stock qty range is: D2:D156679
**I did remove all items from the warehouse list that had a 0 as the quantity, so maybe it would run better if I remove the quantity column since its not relevant and try to say "if "check_stock!B5" is found in "stocksheet!A2:A156679", AND "check_stock!D5:D305" is found in "stocksheet!B2:B156679" THEN color cell (146, 208, 80), if not THEN no fill. I just cant figure out how to do that.**
I appreciate your help in advance. VBA below:
Sub checkStock()
Dim selectedWH As String
Dim checkPartCounter As Integer
Dim stockRowCounter As Integer
Dim checkSheet As Worksheet, stockSheet As Worksheet
Dim foundPart As Boolean, foundPartInStock As Boolean
Set checkSheet = Sheets("check_stock6")
Set stockSheet = Sheets("Stocksheet")
selectedWH = checkSheet.Range("B5")
checkPartCounter = 5
Do While checkSheet.Range("D" & checkPartCounter) <> ""
stockRowCounter = 2
foundPart = False
foundPartInStock = False
Do While stockSheet.Range("A" & stockRowCounter).Value <> ""
If stockSheet.Range("A" & stockRowCounter).Value = selectedWH And stockSheet.Range("B" & stockRowCounter).Value = checkSheet.Range("D" & checkPartCounter).Value Then
foundPart = True
If stockSheet.Range("D" & stockRowCounter).Value > 0 Then
foundPartInStock = True
Else
foundPartInStock = False
End If
Exit Do
End If
stockRowCounter = stockRowCounter + 1
Loop
If foundPartInStock Then
With checkSheet.Range("D" & checkPartCounter).Interior
.Color = RGB(0, 255, 0)
.Pattern = xlSolid
End With
Else
With checkSheet.Range("D" & checkPartCounter).Interior
.Pattern = xlNone
End With
End If
checkPartCounter = checkPartCounter + 1
Loop
End Sub