Relating variables in one array to variables in a second array

[spidaman]

I have two arrays - MyTArray and CtrArray - both of which have 5 variables. The variables refer to 2 corresponding sets of 5 textboxes on the same userform.

How do I make the 5 variables in the first array correspond with the variables in the second array? So I need to relate MyTArray(1) to CtrArray(1), MyTArray(2) to CtrArray(2) and so on.

I am trying to perform a look-up for each value in MyTArray (in another array my4Array) and return a value from the second column in each corresponding textbox in CtrArray.

This is a snippet of the code where I hope you can see what I'm trying to achieve:

For i = 1 To UBound(MyTArray)
    If MyTArray(i) <> "" Then
        For k = LBound(my4Array, 1) To UBound(my4Array, 1)
            If my4Array(k, 1) = Left(MyTArray(1), 4) Then
                For m = 1 To UBound(CtrArray)
                    CtrArray(m).Text = ActiveWorkbook.Sheets("Sheet1").Cells(k, 2).Value
                Next m
            End If
        Next
    End If
Next i

The above is not returning a value in CtrArray textboxes. I know the issue is with the For/Next statement in the middle of the CtrArray as when I run it for a single variable specifying CtrArray(1) for example it does return a correct value.

Can anyone explain how I can relate the MyTArray to CtrArray please so that the look-up values are returned to the corresponding textboxes?

 

[StephenCrump]

A very quick observation (I haven't tried to work out what your code is doing) ....

In Left(MyTArray(1), 4), presumably the 1 should be an i?

 

[Fluff]

Maybe

For i = 1 To UBound(MyTArray)
    If MyTArray(i) <> "" Then
        For k = LBound(my4Array, 1) To UBound(my4Array, 1)
            If my4Array(k, 1) = Left(MyTArray(i), 4) Then
               CtrArray(i).Text = ActiveWorkbook.Sheets("Sheet1").Cells(k, 2).Value
            End If
        Next
    End If
Next i

 

[spidaman]

Well spotted StephenCrump, thanks. The pitfalls of posting after midnight!

Am just about to try Fluff's solution........

 

[spidaman]

Maybe

For i = 1 To UBound(MyTArray)
    If MyTArray(i) <> "" Then
        For k = LBound(my4Array, 1) To UBound(my4Array, 1)
            If my4Array(k, 1) = Left(MyTArray(i), 4) Then
               CtrArray(i).Text = ActiveWorkbook.Sheets("Sheet1").Cells(k, 2).Value
            End If
        Next
    End If
Next i

Thanks for looking Fluff.

Unfortunately still doesn't populate the corresponding textboxes in CtrArray(i)

 

[Fluff]

In that case please post all the code.

 

[spidaman]

In that case please post all the code.

Ok thanks will post this evening

 

[spidaman]

In that case please post all the code.

Here it is Fluff:

Sub Ctr_Search()

Dim UKP As Workbook
Dim Targets(1 To 5), CtrArray(1 To 5), my4Array() As Variant
Dim MyTArray() As String
Dim i, k, m As Long

Set UKP = Workbooks("My_Project.xlsx")

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

Set CtrArray(1) = frmRapid1.txtctr1
Set CtrArray(2) = frmRapid1.txtctr2
Set CtrArray(3) = frmRapid1.txtctr3
Set CtrArray(4) = frmRapid1.txtctr4
Set CtrArray(5) = frmRapid1.txtctr5

my4Array = UKP.Worksheets("allookups").Range("M1:M27").Value

ReDim MyTArray(UBound(Targets)) As String

For i = 1 To UBound(Targets)
MyTArray(i) = CStr(Targets(i))
Next

For i = 1 To UBound(MyTArray)
    If MyTArray(i) <> "" Then
        For k = LBound(my4Array, 1) To UBound(my4Array, 1)
            If my4Array(k, 1) = Left(MyTArray(i), 2) Then
               CtrArray(i).Text = UKP.Worksheets("allookups").Cells(k, 2).Value
            End If
        Next
    End If
Next i

End Sub

I realise that I could look up each item in MyTArray separately and return the result to a specific CtrArray textbox, but I'm trying to make the code more efficient.

Any thoughts?

 

[Fluff]

That code works for me.
What values do you have in M1:M27 & what are you putting in to the txtnum textboxes?

 

[spidaman]

Here it is Fluff:

Sub Ctr_Search()

Dim UKP As Workbook
Dim Targets(1 To 5), CtrArray(1 To 5), my4Array() As Variant
Dim MyTArray() As String
Dim i, k, m As Long

Set UKP = Workbooks("My_Project.xlsx")

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

Set CtrArray(1) = frmRapid1.txtctr1
Set CtrArray(2) = frmRapid1.txtctr2
Set CtrArray(3) = frmRapid1.txtctr3
Set CtrArray(4) = frmRapid1.txtctr4
Set CtrArray(5) = frmRapid1.txtctr5

my4Array = UKP.Worksheets("allookups").Range("M1:M27").Value

ReDim MyTArray(UBound(Targets)) As String

For i = 1 To UBound(Targets)
MyTArray(i) = CStr(Targets(i))
Next

For i = 1 To UBound(MyTArray)
    If MyTArray(i) <> "" Then
        For k = LBound(my4Array, 1) To UBound(my4Array, 1)
            If my4Array(k, 1) = Left(MyTArray(i), 2) Then
               CtrArray(i).Text = UKP.Worksheets("allookups").Cells(k, 2).Value
            End If
        Next
    End If
Next i

End Sub

I realise that I could look up each item in MyTArray separately and return the result to a specific CtrArray textbox, but I'm trying to make the code more efficient.

Any thoughts?

That code works for me.
What values do you have in M1:M27 & what are you putting in to the txtnum textboxes?

The values in

That code works for me.
What values do you have in M1:M27 & what are you putting in to the txtnum textboxes?

M1 to M27 are names. The values in the textboxes are numbers converted to strings in the Redim. Do you think it's an issue with my lookup array?

That code works for me.
What values do you have in M1:M27 & what are you putting in to the txtnum textboxes?