Populate Listview from two regions

silentJ

New Member
Joined
May 29, 2021
Messages
6
Office Version
  1. 2007
Platform
  1. Windows
Hi...

My Listview box is populated by searching through columns B & C to find the cells that match the value of another cell value (my my case x & p), and transferring the data in those rows to the Listview. And this works good.
I need to extend the search range to include columns AB & AC but although I thought this would be straightforward Im not getting any results from the new columns.
The code I thought would work is below. Any help would be appreciated.
Thanks

VBA Code:
Dim wksSource As Worksheet
Dim rngData As Range
Dim rngCell As Range
Dim LstItem As ListItem
Dim RowCount As Long
Dim ColCount As Long
Dim league As String
    
'Set the source
league = Worksheets("Stats").Range("JC1").Value
Set wksSource = Worksheets(league)
Set rngData = wksSource.Range("AA2").CurrentRegion

'Add the column headers
homelistbox.ColumnHeaders.Clear
With Me.homelistbox.ColumnHeaders
    .Add Width:=60
    .Add Width:=171, Alignment:=2
    .Add Width:=171, Alignment:=2
    .Add Width:=20, Alignment:=2
    .Add Width:=20, Alignment:=2
    .Add Width:=9, Alignment:=2
    .Add Width:=20, Alignment:=2
    .Add Width:=20, Alignment:=2
End With

'Count the number of rows in the source range
RowCount = rngData.Rows.Count

'Fill the ListView
Dim x As String
Dim p As String

x = Worksheets("Stats").Range("A5").Value
p = Worksheets("Stats").Range("A6").Value

For i = 2 To RowCount
    If (x = wksSource.Cells(i, "B") And p = wksSource.Cells(i, "C")) Or (x = wksSource.Cells(i, "C") And p = wksSource.Cells(i, "B")) Or (x = wksSource.Cells(i, "AB") And p = wksSource.Cells(i, "AC")) Or (x = wksSource.Cells(i, "AC") And p = wksSource.Cells(i, "AB")) Then
        Set Item = homelistbox.ListItems.Add(Text:=wksSource.Cells(i, 1))
        Item.SubItems(1) = wksSource.Cells(i, 2)
        Item.SubItems(2) = wksSource.Cells(i, 3)
        Item.SubItems(3) = wksSource.Cells(i, 4)
        Item.SubItems(4) = wksSource.Cells(i, 5)
        Item.SubItems(5) = wksSource.Cells(i, 6)
        Item.SubItems(6) = wksSource.Cells(i, 7)
        Item.SubItems(7) = wksSource.Cells(i, 8)
    End If
Next i
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,570
Messages
5,770,913
Members
425,652
Latest member
Pemby

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top