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?