PDA

View Full Version : Userform - Search, Multiple (Different Criteria)



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,

Bob Phillips
11-24-2008, 08:26 AM
Wouldn't you just filter the extra columns accordingly?

phendrena
11-24-2008, 08:27 AM
Wouldn't you just filter the extra columns accordingly?
Going back to my "learning slowly" comment... how would one do this?

Thanks,

Bob Phillips
11-24-2008, 09:11 AM
It would go something like this




If Not .AutoFilterMode Then .Range("A2").AutoFilter
rFilter.AutoFilter FIELD:=1, Criteria1:=strFind
rFilter.AutoFilter FIELD:=3, Criteria1:=strFind2

phendrena
11-25-2008, 03:34 AM
It would go something like this




If Not .AutoFilterMode Then .Range("A2").AutoFilter
rFilter.AutoFilter FIELD:=1, Criteria1:=strFind
rFilter.AutoFilter FIELD:=3, Criteria1:=strFind2


Hi xld,

Thanks for the reply.
I've modified the code as below :-

Sub cmdSearch_Click()
Dim strFind As String
Dim strFind2 As String
Dim strFind3 As String
Dim rFilter As Range
Set rFilter = Range(Sheet2.Range("A1"), Sheet2.Range("K2000").End(xlUp))

Set rng = Range(Sheet2.Range("A1"), Sheet2.Range("A2000").End(xlUp))

strFind = Me.txtDealerCodeSearch.Value
strFind2 = Me.txtDealerCodeSearch.Value
strFind3 = Me.txtDealerCodeSearch.Value
With Sheet2

If Not .AutoFilterMode Then .Range("A1").AutoFilter
rFilter.AutoFilter FIELD:=1, Criteria1:=strFind
rFilter.AutoFilter FIELD:=2, Criteria1:=strFind2
rFilter.AutoFilter FIELD:=3, Criteria1:=strFind3
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

This doesn't appear to work for me.

Ideally the end user will be able to enter either the Dealer Code, System Code or Dealer Name (Either full or partial) and the listbox should be populated accordingly.

Any suggestions?

Thanks,

(edited workbook attached - I've removed a lot of data for confidentiality)

Bob Phillips
11-25-2008, 04:03 AM
Try this

phendrena
11-25-2008, 05:41 AM
Thanks xld,

That does work (occasionaly).

1) When it works :-

Search via the Dealer code only brings up 1 result when there should be duplicates (EG A1368)

It does bring up duplicates when you enter a Dealer Name though. Althought using the Dealer Name Macrae, it still only wants to give 1 result, others work fine such as Vospers, Hendy.

2) When it doesn't want to work :-

It errors with the following :-

Application-defined or object-defined error. Itr then highlights :
.List(.ListCount - 1, 4) = c.Offset(0, 10).Value 'Scheme

Is this a general problem with excel '97 or i am really asking it to do a little too much?

Thanks xld, you support and answers have been very helpful to me.

Bob Phillips
11-25-2008, 06:09 AM
Maybe a problem with 97, but I cannot test that, my 97 machine is kaput at the moment.

phendrena
11-25-2008, 07:37 AM
No panic, it's not a critical project, more of a 'would be nice' kinda thing :)

Thanks for your help so far xld