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.
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!
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.
VBA Code:
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!