phendrena
11-24-2008, 08:18 AM
Hi There,
I'm using the following code to search a worksheet and populate a list box with the results. This works great.
However, I would like to expand the search out to several columns on the worksheet. Can anyone suggest how this can done?
Sub cmdSearch_Click()
Dim strFind As String
Dim rFilter As Range
Set rFilter = Range(Sheet2.Range("A2"), Sheet2.Range("K2000").End(xlUp))
Set rng = Range(Sheet2.Range("A2"), Sheet2.Range("A2000").End(xlUp))
strFind = Me.txtDealerCodeSearch.Value
With Sheet2
If Not .AutoFilterMode Then .Range("A2").AutoFilter
rFilter.AutoFilter FIELD:=1, Criteria1:=strFind
Set rng = rng.Cells.SpecialCells(xlCellTypeVisible)
Me.lbxResults.Clear
For Each c In rng
With Me.lbxResults
.ColumnWidths = "75;75;150;75"
.AddItem c.Value
.List(.ListCount - 1, 1) = c.Offset(0, 1).Value 'AIS Agency Code
.List(.ListCount - 1, 2) = c.Offset(0, 2).Value 'Dealer Name
.List(.ListCount - 1, 3) = c.Offset(0, 6).Value 'Postcode
.List(.ListCount - 1, 4) = c.Offset(0, 10).Value 'Scheme
.List(.ListCount - 1, 5) = c.Offset(0, 3).Value 'Address 1
.List(.ListCount - 1, 6) = c.Offset(0, 4).Value 'Address 2
.List(.ListCount - 1, 7) = c.Offset(0, 5).Value 'Address 3
.List(.ListCount - 1, 8) = c.Offset(0, 7).Value 'Tel No
.List(.ListCount - 1, 9) = c.Offset(0, 9).Value 'Email
End With
Next c
End With
End Sub
Thanks,
I'm using the following code to search a worksheet and populate a list box with the results. This works great.
However, I would like to expand the search out to several columns on the worksheet. Can anyone suggest how this can done?
Sub cmdSearch_Click()
Dim strFind As String
Dim rFilter As Range
Set rFilter = Range(Sheet2.Range("A2"), Sheet2.Range("K2000").End(xlUp))
Set rng = Range(Sheet2.Range("A2"), Sheet2.Range("A2000").End(xlUp))
strFind = Me.txtDealerCodeSearch.Value
With Sheet2
If Not .AutoFilterMode Then .Range("A2").AutoFilter
rFilter.AutoFilter FIELD:=1, Criteria1:=strFind
Set rng = rng.Cells.SpecialCells(xlCellTypeVisible)
Me.lbxResults.Clear
For Each c In rng
With Me.lbxResults
.ColumnWidths = "75;75;150;75"
.AddItem c.Value
.List(.ListCount - 1, 1) = c.Offset(0, 1).Value 'AIS Agency Code
.List(.ListCount - 1, 2) = c.Offset(0, 2).Value 'Dealer Name
.List(.ListCount - 1, 3) = c.Offset(0, 6).Value 'Postcode
.List(.ListCount - 1, 4) = c.Offset(0, 10).Value 'Scheme
.List(.ListCount - 1, 5) = c.Offset(0, 3).Value 'Address 1
.List(.ListCount - 1, 6) = c.Offset(0, 4).Value 'Address 2
.List(.ListCount - 1, 7) = c.Offset(0, 5).Value 'Address 3
.List(.ListCount - 1, 8) = c.Offset(0, 7).Value 'Tel No
.List(.ListCount - 1, 9) = c.Offset(0, 9).Value 'Email
End With
Next c
End With
End Sub
Thanks,