CODEWITHSUNDEEP
excelvba
Dave
Dave

Reputation: 35

Excel Macro Goes into Endless Loop

I've been working on this macro and function for a while. The function works fine when I just run it by itself, but when I try to put it into a For loop it repeats forever and never increments. I'll start with the main macro.

Sub Run()
    Dim shrt As Worksheet: Set shrt = Worksheets("Shortages")
    Dim sTbl As ListObject: Set sTbl = shrt.ListObjects("Shortages_T")
    Dim xRng As Range
    Dim x As Integer

    shrt.Range("H3") = "Can Build"
    
    'x = 9
    For x = 9 To 14 Step 1
        Set xRng = sTbl.ListColumns(x).DataBodyRange
        shrt.Cells(3, x) = BldQty(xRng, x)
    Next x

End Sub

If I comment out the loop and manually increment x, it works fine. Once I put it in the loop, it just endlessly repeats and never increments. I've checked with a Print.Debug. The function below is what I'm trying to call with it. I checked the debug and it just keeps running the same range and column over and over again.

Function BldQty(xRng As Range, scol As Integer) As Long
    Dim shrt As Worksheet: Set shrt = Worksheets("Shortages")
    Dim sTbl As ListObject: Set sTbl = shrt.ListObjects("Shortages_T")
    Dim use As Worksheet: Set use = Worksheets("Useage")
    Dim uTbl As ListObject: Set uTbl = use.ListObjects("UseTable")
    Dim Cell As Range
    Dim mdl As String
    Dim cRng As Range
    Dim qp As Double
    Dim div As Double
    Dim low As Double
    Dim col As Integer
    
    BldQty = xRng(1)
    scol = scol - 1
    
    For Each Cell In xRng
        If Application.WorksheetFunction.Min(xRng) <= 0 Then
            BldQty = 0
            Exit For
        Else
            mdl = Cell.Offset(0, -scol).Value
            'VLOOKUP equivalent
            qp = uTbl.ListColumns("Component").DataBodyRange.Find(mdl).Offset(0, 1)
            div = Cell / qp
            If div < BldQty Then
                BldQty = div
            End If
        End If
    Next Cell
End Function

Am I calling it wrong in the loop?

Upvotes: 0

Views: 147

Answers (1)

Red Hare
Red Hare

Reputation: 687

ByRef instead of ByVal? I'm not sure about what your code shall do, but

shrt.Cells(3, x) = BldQty(xRng, x)

goes to

Function BldQty(xRng As Range, scol As Integer) As Long

where scol ist passed by reference And in this function you change scol so it returns its new value to x Try

Function BldQty(xRng As Range,ByVal scol As Integer) As Long

ByRef is the default in VBA

Upvotes: 1

Related Questions