Nested for loops

[spidaman]

Can anyone help debug my For loops nested in an If function please?

The aim is to perform a look-up on the initial digits of numbers in several userform textboxes (Targets array) and return the value from the second column of two arrays.

Sub Rapid_Search()

Dim Targets(1 To 5), Ctrs(1 To 5) As Variant
Dim i, k, l As Long
Dim my4Array(), my3Array() As Variant

Set Targets(1) = frmRapid1.txtNum1
Set Targets(2) = frmRapid1.txtNum2
Set Targets(3) = frmRapid1.txtNum3
Set Targets(4) = frmRapid1.txtNum4
Set Targets(5) = frmRapid1.txtNum5

my4Array = UKP.Worksheets("allookups").Range("A1:E27").Value
my3Array = UKP.Worksheets("allookups").Range("A28:E191").Value

Set Ctrs(1) = frmRapid1.txtctrs1
Set Ctrs(2) = frmRapid1.txtctrs2
Set Ctrs(3) = frmRapid1.txtctrs3
Set Ctrs(4) = frmRapid1.txtctrs4
Set Ctrs(5) = frmRapid1.txtctrs5

For i = 1 To UBound(Targets)
    If my4Array(k, 1) = Left(Targets(i), 4).Text Then
        For k = 1 To UBound(my4Array, 1)
            For l = 1 To UBound(Ctrs)
                Ctrs(l).Text = UKP.Worksheets("allookups").Cells(k, 2).Value
            Exit For
        Next
    ElseIf my3Array(k, 1) = Left(Targets(i), 3).Text Then
        For k = 1 To UBound(my3Array, 1)
            For l = 1 To UBound(Ctrs)
                Ctrs(l).Text = UKP.Worksheets("allookups").Cells(k, 2).Value
            Exit For
        Next
    End If
Next i

End Sub

Currently I am getting the Else without If compile error. I'm not sure if I can nest the For loop within an If function within another For loop?

Any help much appreciated!

 

[Saurabhj]

Hi, I haven't checked the complete code but the loop structure should be as below:

For i = 1 To UBound(Targets)
    If my4Array(k, 1) = Left(Targets(i), 4).Text Then
        For k = 1 To UBound(my4Array, 1)
            For l = 1 To UBound(Ctrs)
                Ctrs(l).Text = UKP.Worksheets("allookups").Cells(k, 2).Value
                Exit For
            Next
        Next
    ElseIf my3Array(k, 1) = Left(Targets(i), 3).Text Then
        For k = 1 To UBound(my3Array, 1)
            For l = 1 To UBound(Ctrs)
                Ctrs(l).Text = UKP.Worksheets("allookups").Cells(k, 2).Value
                Exit For
            Next
        Next
    End If
Next i

 

[spidaman]

Hi, I haven't checked the complete code but the loop structure should be as below:

For i = 1 To UBound(Targets)
    If my4Array(k, 1) = Left(Targets(i), 4).Text Then
        For k = 1 To UBound(my4Array, 1)
            For l = 1 To UBound(Ctrs)
                Ctrs(l).Text = UKP.Worksheets("allookups").Cells(k, 2).Value
                Exit For
            Next
        Next
    ElseIf my3Array(k, 1) = Left(Targets(i), 3).Text Then
        For k = 1 To UBound(my3Array, 1)
            For l = 1 To UBound(Ctrs)
                Ctrs(l).Text = UKP.Worksheets("allookups").Cells(k, 2).Value
                Exit For
            Next
        Next
    End If
Next i

Thanks Saurabhj, I see the glaring structural error there!