VBA UserForm - "Pasting" array values on individual lines in a textbox

[Alan_P]

Hello All,

I have this code that searches for the selection in a ComboBox in a specific column then "copy & pastes" a cell on the same row into a TextBox (3 cells & 3 text boxes), I'm trying to get each found item on a new line in the text box. The code below works to do this using arrays, but I have only used 5 positions for each array to test the code...

The problem I'm having is that when I want to increase the amount of array positions to say 50, the code for entering the data into the TextBox on individual lines gets very very long! I've tried to create a loop to do it but I keep either getting error codes or only the last array position gets entered into the TextBox...

I'm using Excel 2010 & Windows 7.

Any help is much appreciated!
Cheers,
Alan.

Problem Code:

Engineering.TextBox14.value = MyArray1(0) & vbNewLine & MyArray1(1) & vbNewLine & MyArray1(2) & vbNewLine & MyArray1(3) & vbNewLine & MyArray1(4) & vbNewLine & MyArray1(5)
Engineering.TextBox15.value = MyArray2(0) & vbNewLine & MyArray2(1) & vbNewLine & MyArray2(2) & vbNewLine & MyArray2(3) & vbNewLine & MyArray2(4) & vbNewLine & MyArray2(5)
Engineering.TextBox16.value = MyArray3(0) & vbNewLine & MyArray3(1) & vbNewLine & MyArray3(2) & vbNewLine & MyArray3(3) & vbNewLine & MyArray3(4) & vbNewLine & MyArray3(5)

Complete code:

Sub GetEngData()
Dim MyArray1(5) As String
Dim MyArray2(5) As String
Dim MyArray3(5) As String
Dim i As Long
Dim a As Long

If Application.IsText(Engineering.ComboBox4.value) Then
    
    a = 0
    i = 0
    id = Engineering.ComboBox4.value
    
    Do While Cells(i + 1, 4).value <> ""
    
    
        If Cells(i + 1, 4).value = id Then
            'Routing Title
            MyArray1(a) = Cells(i + 1, 1)
            'Routing Number
            MyArray2(a) = Cells(i + 1, 2)
            'Routing Status
            MyArray3(a) = Cells(i + 1, 3)
            a = a + 1
        End If
          
        i = i + 1
        
    Loop
    
Engineering.TextBox14.value = MyArray1(0) & vbNewLine & MyArray1(1) & vbNewLine & MyArray1(2) & vbNewLine & MyArray1(3) & vbNewLine & MyArray1(4) & vbNewLine & MyArray1(5)
Engineering.TextBox15.value = MyArray2(0) & vbNewLine & MyArray2(1) & vbNewLine & MyArray2(2) & vbNewLine & MyArray2(3) & vbNewLine & MyArray2(4) & vbNewLine & MyArray2(5)
Engineering.TextBox16.value = MyArray3(0) & vbNewLine & MyArray3(1) & vbNewLine & MyArray3(2) & vbNewLine & MyArray3(3) & vbNewLine & MyArray3(4) & vbNewLine & MyArray3(5)

Erase MyArray1
Erase MyArray2
Erase MyArray3

End If

End Sub

 

[Alan_P]

Hi,

Not sure if this is a difficult question or if I've not explained it properly?

Help or feedback appreciated,
Thanks,
Alan.