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
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