Additem problem

[CG Rosén]

Good day Group,

Have following problem. By the code below I try to add all items of range
A1:A10 to
a Listbox. It works OK but the first added item will be the one of cell "A2"
not of cell "A1"
which appears in the bottom of theListbox.

Grateful for some hints.

CG Rosen
----------------------------------------------------------------------
With Sheets("Sheet1").Range("A1:A10")

Set j = .Find("*", _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows)

If Not j Is Nothing Then
firstAddress = j.Address
Do
r = j.Row

Item1 = Sheets("Sheet1").Cells(r, 1)
Item2 = Sheets("Sheet1").Cells(r, 2)

UserForm1.ListBox1.AddItem Item1
UserForm1.ListBox1.List(ListBox1.ListCount - 1, 1) = Item2

Set j = .FindNext(j)
Loop While Not j Is Nothing And j.Address <> firstAddress

End If

End With

 

[Dave Peterson]

Tell it where you want to start and the direction you want to go:

With Sheets("Sheet1").Range("A1:A10")
Set j = .Cells.Find(what:="*", _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)


In fact, it's a very good idea to specify all the parms in your .find
statement. .Find will share those parms with the user (edit|Find). If the user
changes one of those parameters, your code could break.

But it looks like you're grabbing the whole range (a1:b10):

You could use:

Option Explicit
Private Sub UserForm_Initialize()
With Me.ListBox1
.ColumnCount = 2
.List = Worksheets("sheet1").Range("a1:b10").Value
End With
End Sub

or even use the .rowsource property

Option Explicit
Private Sub UserForm_Initialize()
With Me.ListBox1
.ColumnCount = 2
.RowSource _
= Worksheets("sheet1").Range("a1:b10").Address(external:=True)
End With
End Sub

 

[Guest]

Hi,

The problem is that Find always finds the Next value. It skips the current
cell and finds it after it has found all the rest.

Perhaps you should use code something like this (Untested):-

for each c in Sheets("Sheet1").Range("A1:A10")
if c.value <> "" Then
Item1 = c.value
Item2 = c.offset(0,1).Value

UserForm1.ListBox1.AddItem Item1
UserForm1.ListBox1.List(ListBox1.ListCount - 1, 1) = Item2
end if
next c