Can't get Loop to work correctly

[mpadgett]

I'm trying to use the following code to read entries in Column J (starting at row 1) of Sheet 1 and add each value to a list box until a blank entry is found. I can't figure out why it's not working. Help would be appreciated.

Private Sub UserForm_Activate()
Dim Insert_Name
Dim Counter

Counter = 0

Do While Insert_Name <> ""

Counter = Counter + 1
Insert_Name = Worksheets("Sheet1").Cells(Counter, 10).Value
ListBox1.AddItem Insert_Name

Loop

End Sub

 

[SgtJarrow]

Quick glance says that Insert_Name is "" to start....so you need to DO at least one reptition.

Remove the While Insert_Name <> "" from the Do line and add it to the Loop line.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[PHV]

And if you don't want a blank line in your list:
Counter = 0
Insert_Name = Worksheets("Sheet1").Cells(1, 10).Value
Do While Insert_Name <> ""
ListBox1.AddItem Insert_Name
Counter = Counter + 1
Insert_Name = Worksheets("Sheet1").Cells(Counter + 1, 10).Value
Loop

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Zathras]

Here is another approach that may be easier to work with:

Private Sub UserForm_Activate()
Dim List As Range
Dim c As Range
  Set List = Range("J1")
  Set List = Range("J1", List.End(xlDown))
  For Each c In List
     ListBox1.AddItem (c.Value)
  Next c
End Sub

It assumes that you will always have more than one entry in the list. If it is possible for J2 to be empty, then code will be needed to test for that condition.