I'm trying to spread a budget overspend into budgets that have underspent. Currently, my method is to loop through each row and fit negative values into positive ones.
My code is giving me a "Next without for" error, and after looking around StackOverflow and other forums, the cause is an open-ended If
statement, but for the life of me I can't figure out where. Maybe there is a different issue?
Column I in the picture updates based on N:P. The final output is supposed to look like the picture.
Sub SpreadMacro()
'Defining Variables
Dim Lr As Long
Dim rng As Range
Dim row1 As Range
Dim Sp1 As Long
Dim Sp2 As Long
Dim Sp3 As Long
Dim Mtch As Boolean
Dim MtchGL As String
Dim Mx As Long
Dim rng2 As Range
'Find table length
Lr = Cells(Rows.Count, "H").End(xlUp).Row
Set rng = Range("H4:H" & Lr)
'Find negative values and set spread values
For Each row1 In rng.Rows
If row1.Value >= 0 Then
GoTo Skip
Else
Set rng2 = Range("M:" & Lr)
If Application.Evaluate("MAX(rng2)") + row1.Value < 0 Then
row1.Interior.ColorIndex = 8
GoTo Skip
Else
Sp1 = row1.Offset(0, 6)
Sp2 = row1.Offset(0, 7)
Sp3 = row1.Offset(0, 8)
'Find budget value than can fit the variance, and return the cell address and account
Mtch = Application.Evaluate("CELL(""Address"",(INDEX(M:M,MATCH(TRUE,INDEX((M:M+" & row1.Offset(0, 5).Value & ">0),),0))))")
MtchGL = Range(Mtch).Offset(0, -9)
'Insert spread values into the matched account
Range(Mtch).Offset(0, 1).Formula = Range(Mtch).Offset(0, 1).Formula & "+" & Sp1
Range(Mtch).Offset(0, 2).Formula = Range(Mtch).Offset(0, 2).Formula & "+" & Sp2
Range(Mtch).Offset(0, 3).Formula = Range(Mtch).Offset(0, 3).Formula & "+" & Sp3
'Clear the negative variance from over budget account and indicate where the variance was spread
row1.Offset(0, 5).Value = "Spread in " & MtchGL
row1.Offset(0, 6).Value = "0"
row1.Offset(0, 7).Value = "0"
row1.Offset(0, 8).Value = "0"
End If
Skip:
Next row1
End Sub
Your inner-most If
statement is missing a closing End If
after zeroing out the negative values.
If Application.Evaluate("MAX(rng2)") + row1.Value < 0 Then
...
End If ' <-- add this