Searching multiple cells

[Reign]

Hi Everyone,

I was wondering if you could please help me. I have made a database with some code and examples and it's all working from searching just from the bsb with is cell (A) but I was wondering how can I alter the code so I can search in any of the form and it will search the entire sheet for that match.
I was hoping if they search the state say Sydney I want it to post all finds in the list box and if they branch it will only do there specific search..

Thanks for all your help..

I'm not the best at this stuff yet
Atached is my little program which I'm building any help would be great..

[Killian]

Hi and welcome to VBAX :hi:

Essentially, when using Find, you're concerned with 2 things: what you want to find and the range you want to search, so in your codeWith rSearch
Set c = .Find(strFind, LookIn:=xlValues)
'... rSearch is the range your searching, which is currently set with Set rSearch = Sheet1.Range("a6", Range("a65536").End(xlUp))which is the BSB column and the text you search for is the BSB textbox value.

One approach therefore, would be to change your Find routine to one that takes two arguments - the seach string and the range to search - and have a Find button next to each field on the form, that passes those arguments.

So, lets remove the FindAll command button, but keep its code, and change the declaration'from
'Private Sub cmbFindAll_Click()
'to
Private Sub FindAll(rSearch As Range, strFind As String)and now we're providing the range and string, comment out the variable declarations and the lines that set them'Dim strFind As String 'what to find
'Dim rSearch As Range 'range to search
'and
'Set rSearch = Sheet1.Range("a6", Range("a65536").End(xlUp))
'strFind = Me.TextBox1.ValueNow we can call the routine from with however many buttons we want to add for different searches.
To do a search by state, add a button "cmdFindState" and call the routine passing the range for the state data column and state textbox valuePrivate Sub cmdFindState_Click()
FindAll Sheet1.Range("d6", Range("d65536").End(xlUp)), Me.TextBox4.Value
End SubThat should do it I reckon.

[Reign]

Sick thanks for that it helped heaps.

[Reign]

Thanks everything is working but how would I make it so all the information in the cells (A,B,C,D,E) be displayed as in the form and the list box it's not actually showing all my information..

Thanks.

[Killian]

Ahh, yes, I'd didn't pay to much attention to the rest of the code...
I've re-jigged that routine a little to build each list entry for each find, it seems a little more straight-forward than making an array. Also, AFAIK, the column headings only work if you use the rowsource property to return a range direct from the worksheet to populate the listbox.Private Sub FindAll(rSearch As Range, strFind As String)

Dim FirstAddress As String
Dim i As Integer
Dim j As Integer
i = 0
With rSearch
Set c = .Find(strFind, LookIn:=xlValues)
If Not c Is Nothing Then 'found it
FirstAddress = c.Address
Do
ListBox1.AddItem
For j = 0 To 4
ListBox1.List(i, j) = Cells(c.Row, j + 1)
Next j
i = i + 1
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
End With

End Sub