Userform populate Textbox value or Label caption from an Array

[netuser]

Hi,

I have looped through certain range of cells to put value in an array. Now I want to put these values inside a Textbox with multiple lines or in a Label Caption.

I searched like crazy on Google but I find nothing.

Can anyone help please I need to finish a project and I am stuck because of this.

Dim arr() As Variant, i As Integer, LastRow, Rng As Range
 
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Set Rng = Worksheets("test").Range("A2:A" & LastRow)
 
i = 0
ReDim arr(0)
For Each cell In Rng
   
    If Range("C" & cell.Row) = "xyz" Then
    arr(i) = cell & "-" & Range("B" & cell.Row) & vbCrLf
    i = i + 1
    End If
    ReDim Preserve arr(i)
Next
 
‘Getting error here:
TextBox1.Text = arr

Thanks for your help J

 

[MickG]

MayBe!!!

[COLOR="Navy"]Sub[/COLOR] MG19Jul56
[COLOR="Navy"]Dim[/COLOR] LastRow, Rng [COLOR="Navy"]As[/COLOR] Range, cell [COLOR="Navy"]As[/COLOR] Range, nStr [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
 
LastRow = Range("A" & Rows.Count).End(xlUp).Row
[COLOR="Navy"]Set[/COLOR] Rng = Worksheets("test").Range("A2:A" & LastRow)
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] cell [COLOR="Navy"]In[/COLOR] Rng
   [COLOR="Navy"]If[/COLOR] Range("C" & cell.Row) = "xyz" [COLOR="Navy"]Then[/COLOR]
      nStr = nStr & cell & "-" & Range("B" & cell.Row) & vbCrLf
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
 
'[COLOR="Green"][B]‘Getting error here:[/B][/COLOR]
[COLOR="Navy"]With[/COLOR] TextBox1
    .Text = ""
    .Text = nStr
    .MultiLine = True
    .WordWrap = True
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[My Aswer Is This]

I was about done on this when Mick gave a similar answer:
Here is my script:

Private Sub CommandButton2_Click()
'Modified  7/19/2018  1:12:52 PM  EDT
Application.ScreenUpdating = False
Dim c As Range
Dim ans As String
Dim Lastrow As Long
TextBox1.MultiLine = True
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    For Each c In Cells(1, 1).Resize(Lastrow)
        If c.Value = "xyz" Then ans = ans & c.Value & "-" & c.Offset(, 1).Value & vbNewLine
    Next
TextBox1.Value = ans
Application.ScreenUpdating = True
End Sub

 

[My Aswer Is This]

I forgot about sheet name being test:
Try this:

Private Sub CommandButton2_Click()
'Modified  7/19/2018  1:35:28 PM  EDT
Application.ScreenUpdating = False
Dim c As Range
Dim ans As String
Dim Lastrow As Long
TextBox1.MultiLine = True
With Sheets("test")
    Lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
    For Each c In .Cells(1, 1).Resize(Lastrow)
        If c.Value = "xyz" Then ans = ans & c.Value & "-" & c.Offset(, 1).Value & vbNewLine
    Next
End With
TextBox1.Value = ans
Application.ScreenUpdating = True
End Sub

 

[netuser]

Thank you so much both of you , working perfectly

 

[My Aswer Is This]

Thank you so much both of you , working perfectly

Glad we were able to help you.
Come back here to Mr. Excel next time you need additional assistance.