Userform Loop

[Jietoh85]

HI,

I'm trying to write a simple loop to pull data from my worksheet into a text box on my userform when I click a command button.

The data is all in one column, but it could sometimes only be 3 rows, or 10. Once I get the data into the text box I want it formatted like this:

"Data from H2"
"Data from H3"
"Data from H4"

...and so on until it is done then I'll add additional text at the very bottom.

I know this should be simple, but I can't seem to find the solution today.

TIA

 

[Skyybot]

Does Column H have a header?

 

[Jietoh85]

Does Column H have a header?

Yes - "Billing String"

 

[Jietoh85]

Ping

 

[Skyybot]

Ping

Apologies. I will work on this today.

 

[Jietoh85]

Apologies. I will work on this today.

Thanks! I really appreciate your support!

 

[Skyybot]

Sorry for the delay. I've come up with something that I think may work. The following code assumes that your UserForm and code reside in one (the currently used) VBAProject and it is called UserForm1. It also assumes that your data is on Sheet1 of your WorkBook. If it's named something else you will have to edit the code. Other than that, let'er rip. Let me know what happens.

Option Base 1
Sub frmTest()
Dim wb As Workbook, sht As Worksheet, rng As Range, cell As Range
Dim ctrs As Controls, myFrm As UserForm, ctr As Control
Dim d(), i As Long, lBox
Set myFrm = UserForm1: Set ctrs = UserForm1.Controls
Set wb = ThisWorkbook: Set sht = wb.Sheets("Sheet1")
Set rng = sht.Range(Cells(2, 8), Cells(sht.UsedRange.Rows.Count, 8))
For Each cell In rng
    If Not IsEmpty(cell) Then i = i + 1
Next cell
ReDim d(i)
Set rng = sht.Range(Cells(2, 8), Cells(i + 1, 8))
i = 1
For Each cell In rng
    d(i) = cell.Value
    i = i + 1
Next cell
Set lBox = UserForm1.Controls.Add("Forms.ListBox.1", "DataBox")
For i = 1 To UBound(d)
    lBox.AddItem (d(i))
Next i
UserForm1.Show
'Unload UserForm1
End Sub