How can I make this VBA not return an "overflow" error. (VBA included)

[seth777]

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

 

[Joe4]

Possibly the issue is with your variable declarations:

[COLOR=#333333]Dim checkPartCounter As Integer[/COLOR]
[COLOR=#333333]Dim stockRowCounter As Integer[/COLOR]

You mentioned row 156679. Integers only go up to 32767.
So you may need to declare those variables as Long instead.
See: https://msdn.microsoft.com/en-us/library/office/gg251528.aspx

 

[seth777]

Possibly the issue is with your variable declarations:

[COLOR=#333333]Dim checkPartCounter As Integer[/COLOR]
[COLOR=#333333]Dim stockRowCounter As Integer[/COLOR]

You mentioned row 156679. Integers only go up to 32767.
So you mean need to declare those variables as Long instead.
See: https://msdn.microsoft.com/en-us/library/office/gg251528.aspx

That makes sense. I didn't know that. Thank you!

 

[dssJones]

It is easier for the VB interpreter to avoid conversions if possible.
Refer to a cell with CELLS(rowNbr, colNbr), instead of RANGE("D" & rownbrVariable)
Hence cell A5 would be CELLS(5, 1). Especially nice when iterating row and/or column numbers
Please pardon the rowNbr name changes; It just makes it easier for my old eyes.
This notation can speed things up a bit!

Sub checkStock()
    Dim selectedWH As String
    
    Dim part_rowNbr As Long
    Dim stock_rowNbr As Long
    
    Dim checkSheet As Worksheet
    Set checkSheet = Sheets("check_stock6")
    
    Dim stockSheet As Worksheet
    Set stockSheet = Sheets("Stocksheet")
    
    Dim foundPart As Boolean
    Dim foundPartInStock As Boolean
    
    selectedWH = checkSheet.Cells(5, 2).Value
    part_rowNbr = 5
    
    Do While checkSheet.Cells(part_rowNbr, 4).Value <> vbNullString
        stock_rowNbr = 2
        foundPart = False
        foundPartInStock = False
        
        Do While stockSheet.Cells(stock_rowNbr, 1).Value <> vbNullString
            If stockSheet.Cells(stock_rowNbr, 1).Value = selectedWH And _
               stockSheet.Cells(stock_rowNbr, 2).Value = checkSheet.Cells(part_rowNbr, 4).Value Then
                
                foundPart = True
                foundPartInStock = (stockSheet.Sheet(stock_rowNbr, 4).Value > 0)
                Exit Do
            End If
            stock_rowNbr = stock_rowNbr + 1
        Loop
        
        With checkSheet.Cells(part_rowNbr, 4).Interior
            If foundPartInStock Then
                .Color = RGB(0, 255, 0)
                .Pattern = xlSolid
            Else
                .Pattern = xlNone
            End If
        End With
        part_rowNbr = part_rowNbr + 1
    Loop
 End Sub