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>
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
You cannot AutoFilter using more than two criteria, maybe use AdvancedFilter.

Has your data got 48 columns of data?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top