Using Find method in Excel passing more than 1 parameter

[Nick]

HI all

i have an excel worksheet and i need to search on this sheet
passing 3 parameters. HOw can I do this using VBA?


I used the Find method, but i can search using only 1 parameter and i
need to use 2 or 3 parameters passed by form.

Thanks to anyone that wants to help me.
Nick

 

[Tom Ogilvy]

Search (using Find) on the parameter that would be least likely to occur
(most unique). then for each one found, check the two other parameters.
When all three are satisfied, then you are there.

 

[crispbd]

This macro below will allow you to add as many search criteria as yo
like.
It will select all values (either constants or formula values) tha
match. You can modify the code to work with a form, with a next butto
for instance, that would allow you to look at each cell that passed th
criteria.

The current example looks for values greater than a min value, les
than a max value. Paste the below code into a module, then populat
your excel sheet with random numbers and give it a try.

Sub MS()
Dim myrange As Object
Dim myrange2 As Object
Dim myFinishedRange As Object
Dim rngConstants As Object, rngFormulas As Object
Dim MinVal, MaxVal
' set your criteria here
MinVal = 500
MaxVal = 1000
On Error Resume Next
Set rngConstants = ActiveSheet.Cells.SpecialCells(xlConstants)
Set rngFormulas = ActiveSheet.Cells.SpecialCells(xlFormulas)
For Each cellz In rngConstants
'/// put your search criteria here
If (cellz.Value >= MinVal And cellz.Value <= MaxVal) Then
'///
If n = 0 Then
Set myrange = cellz
n = 1
Else
Set myrange = Union(myrange, cellz)
End If
End If
Next cellz
myrange.Select
On Error GoTo endit
n = 0
For Each cellz In rngFormulas
'// same criteria here
If (cellz.Value >= MinValue And cellz.Value <= MaxVal) Then
'//
If n = 0 Then
Set myrange2 = cellz
n = 1
Else
Set myrange2 = Union(myrange2, cellz)
End If
End If
Next cellz
On Error Resume Next
Set myFinishedRange = Union(myrange, myrange2)
myFinishedRange.Select
Exit Sub
endit:
Set myFinishedRange = myrange
If myrange Is Nothing Then Exit Sub
myFinishedRange.Select
End Su

 

[Tom Ogilvy]

You turn off your error handling too soon. If there are no formulas or no
constants, then you final union will error.

Just some other things which might be considered with a looping approach.
loops would be slow for large amounts of data.

And considerations for using specialcells (although I like them as well).
consideration of the 8192 problem with special cells. (Specialcells
fails silently if there are more then 8192 separate areas).