abin kurian
Reputation: 41
Next without for in nested for loop, excel VBA , error
I have 3 nested loops within each other. the excel sheet has 3 sheets of accident data. For example the first sheet has 1 accident involving 2 cars which would be in the second sheet (second sheet has 2 vehicles for 1 accident). The third sheet contains passenger details ( 2 passenger each therefore 4 rows for each accident). I am getting a next without for in this case. I am not sure where i went wrong. I would like assistance in this matter.
Public Function table13()
Dim acctyp, n, tv, toi, t1(8), t2(8), t3(8), t4(8), t5(8), t6(8), t7(8) As Integer
n = ThisWorkbook.Worksheets(7).Cells(Rows.Count, "A").End(xlUp).Row
For i = 0 To 7
t1(i) = 0
t2(i) = 0
t3(i) = 0
t4(i) = 0
t5(i) = 0
t6(i) = 0
t7(i) = 0
Next i
For i = 2 To n
acctyp = ThisWorkbook.Worksheets(7).Cells(i, "P").Value
For veh = 2 To ThisWorkbook.Worksheets(10).Cells(Rows.Count, "A").End(xlUp).Row
If ThisWorkbook.Worksheets(10).Cells(j, "A").Value = ThisWorkbook.Worksheets(7).Cells(i, "A").Value Then
tv = ThisWorkbook.Worksheets(10).Cells(j, "I").Value
If acctyp = 1 Then
If tv = 1 Then
t1(0) = t1(0) + 1
ElseIf tv = 2 Then
t2(0) = t2(0) + 1
ElseIf tv = 3 Then
t3(0) = t3(0) + 1
ElseIf tv = 4 Then
t4(0) = t4(0) + 1
ElseIf tv = 5 Then
t5(0) = t5(0) + 1
ElseIf tv = 6 Then
t6(0) = t6(0) + 1
ElseIf tv = 99 Then
t7(0) = t7(0) + 1
End If
ElseIf acctyp = 2 Then
If tv = 1 Then
t1(1) = t1(1) + 1
ElseIf tv = 2 Then
t2(1) = t2(1) + 1
ElseIf tv = 3 Then
t3(1) = t3(1) + 1
ElseIf tv = 4 Then
t4(1) = t4(1) + 1
ElseIf tv = 5 Then
t5(1) = t5(1) + 1
ElseIf tv = 6 Then
t6(1) = t6(1) + 1
ElseIf tv = 99 Then
t7(1) = t7(1) + 1
End If
ElseIf acctyp = 3 Then
If tv = 1 Then
t1(2) = t1(2) + 1
ElseIf tv = 2 Then
t2(2) = t2(2) + 1
ElseIf tv = 3 Then
t3(2) = t3(2) + 1
ElseIf tv = 4 Then
t4(2) = t4(2) + 1
ElseIf tv = 5 Then
t5(2) = t5(2) + 1
ElseIf tv = 6 Then
t6(2) = t6(2) + 1
ElseIf tv = 99 Then
t7(2) = t7(2) + 1
End If
ElseIf acctyp = 4 Then
If tv = 1 Then
t1(3) = t1(3) + 1
ElseIf tv = 2 Then
t2(3) = t2(3) + 1
ElseIf tv = 3 Then
t3(3) = t3(3) + 1
ElseIf tv = 4 Then
t4(3) = t4(3) + 1
ElseIf tv = 5 Then
t5(3) = t5(3) + 1
ElseIf tv = 6 Then
t6(3) = t6(3) + 1
ElseIf tv = 99 Then
t7(3) = t7(3) + 1
End If
End If
toi = ThisWorkbook.Worksheets(10).Cells(j, "J").Value
If toi = 1 Then
If tv = 1 Then
If tv = 1 Then
t1(5) = t1(5) + 1
ElseIf tv = 2 Then
t2(5) = t2(5) + 1
ElseIf tv = 3 Then
t3(5) = t3(5) + 1
ElseIf tv = 4 Then
t4(5) = t4(5) + 1
ElseIf tv = 5 Then
t5(5) = t5(5) + 1
ElseIf tv = 6 Then
t6(5) = t6(5) + 1
ElseIf tv = 99 Then
t7(5) = t7(5) + 1
End If
ElseIf toi = 2 Then
If tv = 1 Then
t1(6) = t1(6) + 1
ElseIf tv = 2 Then
t2(6) = t2(6) + 1
ElseIf tv = 3 Then
t3(6) = t3(6) + 1
ElseIf tv = 4 Then
t4(6) = t4(6) + 1
ElseIf tv = 5 Then
t5(6) = t5(6) + 1
ElseIf tv = 6 Then
t6(6) = t6(6) + 1
ElseIf tv = 99 Then
t7(6) = t7(6) + 1
End If
ElseIf toi = 3 Then
If tv = 1 Then
t1(7) = t1(7) + 1
ElseIf tv = 2 Then
t2(7) = t2(7) + 1
ElseIf tv = 3 Then
t3(7) = t3(7) + 1
ElseIf tv = 4 Then
t4(7) = t4(7) + 1
ElseIf tv = 5 Then
t5(7) = t5(7) + 1
ElseIf tv = 6 Then
t6(7) = t6(7) + 1
ElseIf tv = 99 Then
t7(7) = t7(7) + 1
End If
End If
For pass = 2 To ThisWorkbook.Worksheets(11).Cells(Rows.Count, "A").End(xlUp).Row
If ThisWorkbook.Worksheets(11).Cells(k, "A").Value = ThisWorkbook.Worksheets(9).Cells(j, "A").Value Then
If ThisWorkbook.Worksheets(11).Cells(k, "C").Value = ThisWorkbook.Worksheets(10).Cells(j, "C").Value Then
toi = ThisWorkbook.Worksheets(11).Cells(k, "I").Value
If toi = 1 Then
If tv = 1 Then
If tv = 1 Then
t1(5) = t1(5) + 1
ElseIf tv = 2 Then
t2(5) = t2(5) + 1
ElseIf tv = 3 Then
t3(5) = t3(5) + 1
ElseIf tv = 4 Then
t4(5) = t4(5) + 1
ElseIf tv = 5 Then
t5(5) = t5(5) + 1
ElseIf tv = 6 Then
t6(5) = t6(5) + 1
ElseIf tv = 99 Then
t7(5) = t7(5) + 1
End If
ElseIf toi = 2 Then
If tv = 1 Then
t1(6) = t1(6) + 1
ElseIf tv = 2 Then
t2(6) = t2(6) + 1
ElseIf tv = 3 Then
t3(6) = t3(6) + 1
ElseIf tv = 4 Then
t4(6) = t4(6) + 1
ElseIf tv = 5 Then
t5(6) = t5(6) + 1
ElseIf tv = 6 Then
t6(6) = t6(6) + 1
ElseIf tv = 99 Then
t7(6) = t7(6) + 1
End If
ElseIf toi = 3 Then
If tv = 1 Then
t1(7) = t1(7) + 1
ElseIf tv = 2 Then
t2(7) = t2(7) + 1
ElseIf tv = 3 Then
t3(7) = t3(7) + 1
ElseIf tv = 4 Then
t4(7) = t4(7) + 1
ElseIf tv = 5 Then
t5(7) = t5(7) + 1
ElseIf tv = 6 Then
t6(7) = t6(7) + 1
ElseIf tv = 99 Then
t7(7) = t7(7) + 1
End If
End If
End If
End If
Next pass
End If
Next veh
Next i
End Function
Upvotes: 0
Views: 76
Answers (1)
DisplayName
Reputation: 13386
sticking to the very subject of the question you missed closing an "If-Then" block
you have:
...
toi = ThisWorkbook.Worksheets(10).Cells(j, "J").Value
If toi = 1 Then
If tv = 1 Then
If tv = 1 Then
t1(5) = t1(5) + 1 '<<=== missing End If
ElseIf tv = 2 Then
...
one possible fix is:
...
toi = ThisWorkbook.Worksheets(10).Cells(j, "J").Value
If toi = 1 Then
If tv = 1 Then
If tv = 1 Then t1(5) = t1(5) + 1
ElseIf tv = 2 Then
...
another possible fix is:
...
If toi = 1 Then
If tv = 1 Then
If tv = 1 Then
t1(5) = t1(5) + 1
End If
ElseIf tv = 2 Then
...
While trying you may consider adopting a 2D array and Select Case syntax to simplify your code
for example:
Dim acctyp As Long, n As Long, i As Long, j As Long, tv As Long, toi As Long, t(1 To 7, 0 To 7) As Long '-<<== use a 2D t(,) array of `Long` s
n = ThisWorkbook.Worksheets(7).Cells(Rows.Count, "A").End(xlUp).Row
' just for demonstrative purposes, while you may skip this double loop since an array of Long type gets initialized with zeros by default
For i = 1 To 7
For j = 0 To 7
t(i, j) = 0
Next
Next
For i = 2 To n
acctyp = ThisWorkbook.Worksheets(7).Cells(i, "P").Value
For veh = 2 To ThisWorkbook.Worksheets(10).Cells(Rows.Count, "A").End(xlUp).Row
If ThisWorkbook.Worksheets(10).Cells(j, "A").Value = ThisWorkbook.Worksheets(7).Cells(i, "A").Value Then
tv = ThisWorkbook.Worksheets(10).Cells(j, "I").Value
Select Case acctyp
Case 1 To 4
Select Case tv
Case 1 To 6
t(tv, acctyp - 1) = t(tv, acctyp - 1) + 1
Case 99
t(7, acctyp - 1) = t(7, acctyp - 1) + 1
End Select
End Select
Upvotes: 1
Related Questions
- For inside another For, Next without For Error
- Next without For error using if statements
- Next without for error in excel vba in loop
- Excel VBA: "Next Without For" Error
- How to fix "compile error: next without for" error in VBA?
- Next Without For Error VBA
- Loop Error (Next without For)
- Next Without For / For Without Next Error
- Next without For Error - What is wrong with this code?
- Next Without For error in nested loop in Excel VBA