Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Additem problem
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Additem problem
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(exter nal:=True) End With End Sub "CG Rosén" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Additem problem
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 "CG Rosén" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Listbox AddItem problem | Excel Programming | |||
.additem | Excel Programming | |||
dropdownmenu with additem | Excel Programming | |||
Please help with AddItem method | Excel Programming | |||
.additem | Excel Programming |