Search Worksheet VBA code. Assistance Needed Please!

[ShezzyWiggle]

Hi All,

Im wondering if anyone could help me please? Im relatively new to VBA after stumbling across it at work and have realised how useful it can be. I work with a lot of data, and am currently trying to create an effective management system that people will be able to use to search for individual claims etc.

I have currently managed to create a user form which can be opened through using a command button on the home screen, and it has 4 text boxes in which you can enter varying search criteria. I am at the stage where I am working on the code for the 'search' command button to get it to search a particular worksheet for matches. The ultimate aim of this is to then have any search matches to display in a listbox on the userform, which when a record is selected will display the 'row' of data in a data form. (Does that make any sense?).

Like I say I am quite new to VBA and have managed to get the following code down (below), by lots of trial and error but now everytime I run it I get an array of errors and have hit a bit of a brick wall with it. If anybody would be able to help me/point me in the right direction, I would very much appreciate it.

Thanks in advance guys

-------

Private Sub CommandButton1_Click()</SPAN>

Dim oWS As Excel.Worksheet</SPAN>
Application.ScreenUpdating = False</SPAN>
On Error GoTo ErrHandler</SPAN>
Do Until (cell = Range("E3850"))</SPAN>

Set oWS = Worksheets("Information")</SPAN>
With oWS</SPAN>
.UsedRange.AutoFilter Field:=1, Criteria1:=TextBox1.Text</SPAN>
.UsedRange.AutoFilter Field:=2, Criteria2:=TextBox2.Text</SPAN>
.UsedRange.AutoFilter Field:=3, Criteria3:=TextBox3.Text</SPAN>
.UsedRange.AutoFilter Field:=4, Criteria4:=TextBox4.Text</SPAN>
If .UsedRange.SpecialCells(xlCellTypeVisible).EntireRow.Rows.Count <> 1 Then</SPAN>
MsgBox "None, or multiple, matches found...", vbExclamation</SPAN>
Else</SPAN>
With ListBox1</SPAN>
.AddItem cell.Offset(0, 46).Value</SPAN>
.List(0, 1) = cell.Value</SPAN>
.List(0, 2) = cell.Offset(0, 48).Value</SPAN>
.List(0, 3) = cell.Offset(0, 46).Address</SPAN>

Set cell = sh.Range("B2:E3850").FindNext(cell)</SPAN>
Loop While cell.Address <> sAddr</SPAN>
Application.ScreenUpdating = True</SPAN>

End If</SPAN>
End With</SPAN>
End Sub</SPAN>

 

[royUK]

You cannot AutoFilter using more than two criteria, maybe use AdvancedFilter.

Has your data got 48 columns of data?

 

[ShezzyWiggle]

Hi,

Thank you for your reply. I didnt realise you couldn't auto filter on more than 2 criteria, but ill see what I can do with that.

Its quite complicated, the data can in some situations run up to about 50 columns (it gets added to a lot), but thinking about it really, I only want the search be based around non variable data, such as Forename, Surname, DOB etc which when an accurate match is found those basic details display in the list box, and that 'record' can be selected.

I apologise if this doesnt make sense, like I say - I am new to VBA so dont know what I can and cant do.

Any help that may be given here is much appreciated.

 

[royUK]

50 Columns is a lot of columns, I'm not even sure if you can autofilter 50 columns of data.
Can you not use less columns, are all the columns necessary?

Take a look at the DatabaseForm example here. The Advanced version has been successfully used on to create a form that works with over twenty columns.