Search Worksheet VBA code. Assistance Needed Please!


New Member
Mar 5, 2014
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>
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>


Well-known Member
Jul 24, 2002
You cannot AutoFilter using more than two criteria, maybe use AdvancedFilter.

Has your data got 48 columns of data?


New Member
Mar 5, 2014

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.


Well-known Member
Jul 24, 2002
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.

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...