VBA function "Next without For" issue

[YanAK]

Hey good ppl, I have this VBA function and I don't know why it's keep saying "Next without For". I'm new in this VBA thig, idk what the problem and how to fix it.
will appreciate your help!
Thanx!!!

Function test(CritRng As Range, rng5 As Range, rng4 As Range, rng3 As Range, rng2 As Range, _
rng1 As Range, Threshold As Variant) As Variant
Dim i As Long
Dim ArrayResult(4) As Variant
For i = 1 To CritRng.Rows.count
If CritRng(i) = 1 Then
If rng5(i) < Threshold Then
ArrayResult(4) = ArrayResult(4) + rng5(i)
Else
ArrayResult(4) = Threshold
    End If

ElseIf CritRng(i) = 2 Then
If rng4(i) < (Threshold * 2) Then
ArrayResult(3) = ArrayResult(3) + rng4(i)
Else
ArrayResult(3) = Threshold
    End If

ElseIf CritRng(i) = 4 Then
If rng3(i) < (Threshold * 4) Then
ArrayResult(2) = ArrayResult(2) + rng3(i)
Else
ArrayResult(2) = Threshold
    End If

ElseIf CritRng(i) = 8 Then
If rng2(i) < (Threshold * 8) Then
ArrayResult(1) = ArrayResult(1) + rng2(i)
Else
ArrayResult(1) = Threshold
    End If

ElseIf CritRng(i) = 16 Then
If rng1(i) < (Threshold * 16) Then
ArrayResult(0) = ArrayResult(0) + rng1(i)
Else
        ArrayResult(0) = Threshold
        End If
Next i
ArrayResult(0) = WorksheetFunction.RoundUp(ArrayResult(0) / 16, 0)
ArrayResult(1) = WorksheetFunction.RoundUp(ArrayResult(1) / 8, 0)
ArrayResult(2) = WorksheetFunction.RoundUp(ArrayResult(2) / 4, 0)
ArrayResult(3) = WorksheetFunction.RoundUp(ArrayResult(3) / 2, 0)
ArrayResult(4) = WorksheetFunction.RoundUp(ArrayResult(4) / 1, 0)
test = Application.Transpose(ArrayResult)
End Function

 

[Fluff]

The problem is you are missing an End If, if you properly indent your code, it makes it easier to spot these sorts of problems.

Function test(CritRng As Range, rng5 As Range, rng4 As Range, rng3 As Range, rng2 As Range, _
   rng1 As Range, Threshold As Variant) As Variant
   Dim i As Long
   Dim ArrayResult(4) As Variant
   For i = 1 To CritRng.Rows.Count
      If CritRng(i) = 1 Then
         If rng5(i) < Threshold Then
            ArrayResult(4) = ArrayResult(4) + rng5(i)
         Else
            rrayResult(4) = Threshold
         End If
      ElseIf CritRng(i) = 2 Then
         If rng4(i) < (Threshold * 2) Then
            ArrayResult(3) = ArrayResult(3) + rng4(i)
         Else
            ArrayResult(3) = Threshold
         End If
      ElseIf CritRng(i) = 4 Then
         If rng3(i) < (Threshold * 4) Then
            ArrayResult(2) = ArrayResult(2) + rng3(i)
         Else
            ArrayResult(2) = Threshold
         End If
      ElseIf CritRng(i) = 8 Then
         If rng2(i) < (Threshold * 8) Then
            ArrayResult(1) = ArrayResult(1) + rng2(i)
         Else
            ArrayResult(1) = Threshold
         End If
      ElseIf CritRng(i) = 16 Then
         If rng1(i) < (Threshold * 16) Then
            ArrayResult(0) = ArrayResult(0) + rng1(i)
         Else
            ArrayResult(0) = Threshold
         End If
      End If
   Next i
   ArrayResult(0) = WorksheetFunction.RoundUp(ArrayResult(0) / 16, 0)
   ArrayResult(1) = WorksheetFunction.RoundUp(ArrayResult(1) / 8, 0)
   ArrayResult(2) = WorksheetFunction.RoundUp(ArrayResult(2) / 4, 0)
   ArrayResult(3) = WorksheetFunction.RoundUp(ArrayResult(3) / 2, 0)
   ArrayResult(4) = WorksheetFunction.RoundUp(ArrayResult(4) / 1, 0)
   test = Application.Transpose(ArrayResult)
End Function

 

[JLGWhiz]

After all these years, you would think that MS would have done something about that particular error message. It is particularly confusing to new coders in VBA. They spend hours looking for the wrong missing element in some cases.

 

[Fluff]

It is particularly confusing to new coders in VBA

Totally agree, but gives us something to do

 

[YanAK]

Thanks guyz! that was silly to ask lol!

 

[JLGWhiz]

Thanks guyz! that was silly to ask lol!

It is never silly to ask a question you don't know the answer to. But I can tell you that Microsoft does not produce perfect applications. There are a lot of quirks in their products that they have never fixed. They do not impair the overall effectiveness of the applications, but they can be very annoying when first encountered by a novice.