Search Worksheet VBA code. Assistance Needed Please!

ShezzyWiggle

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

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

Has your data got 48 columns of data?
 

ShezzyWiggle

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

Well-known Member
Joined
Jul 24, 2002
Messages
1,605
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

Threads
1,082,283
Messages
5,364,268
Members
400,787
Latest member
bs04c

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