Populating an Excel ListView control using VBA 1

[JasonEnsor]

Hi Guys,

I am hoping someone can shed some light on an issue I am having at the moment. I have a userform that hosts a listview control. I am wanting to loop through all cells on my worksheet and populate them in the listview control called lst (Once I have that working I will set up criteria to only populate certain values).

The code I have at the moment is

For Each Item In ws.Range("A2:A" & currentLastRow)
        currentRow = Item.Row
        lst.ListItems.Add lst.ListItems.count, , Item.Cells(currentRow, 1)
    Next Item

This gives me an Index out of bounds error.

if I alter my code to the following

For Each Item In ws.Range("A2:A" & currentLastRow)
        currentRow = Item.Row
        lst.ListItems.Add lst.ListItems.count + 1, , Item.Cells(currentRow, 1)
    Next Item

and have the values 1 to 6 in column A, I get the numbers 2,4,6 printed then 3 blank lines on the lst control

Any ideas what is going wrong here with my code?

Any help would be appreciated


Regards

J.

 

[Andrzejek]

You can help yourself by placing a breake in your code and step thru it. You will SEE what's going on and where you have a problem.

Have fun.

---- Andy

 

[combo]

The Range.Cells(...) reference is relative to the Range. Item.Cells(currentRow,1) returns a value that is CurrentRow-1 cells below the Item, that is probably different from what you need. To pick the contents of Item use Item.Cells(1,1).

combo

 

[JasonEnsor]

Hi Guys,

I have sorted this issue now so I thought I would share the code I used. I had tried using a breakpoint before I submitted code here asking for help but couldn't fathom out what was going wrong.

Thank you Combo, I believe the Range was the issue. The code snippet below populates all my desired values to the listbox and displays them as desired.

[CODE VBA]
lst.ListItems.Clear
For Each item In ws.Range("A2:A" & currentLastRow)


currentRow = item.Row


If dayTab.SelectedItem.Caption = Format$(ws.Cells(currentRow, 1), "ddd") Then

lst.ListItems.Add (1), , ws.Cells(currentRow, 1)

For i = 1 To 14
lst.ListItems(1).ListSubItems.Add , , ws.Cells(currentRow, i + 1)
Next i

End If
Next item
[/CODE]

Many Thanks Again guys

Regards

J.