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