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

--
Regards,
Tom Ogilvy


"crispbd" wrote in message
...

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

The current example looks for values greater than a min value, less
than a max value. Paste the below code into a module, then populate
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 Sub


--
crispbd
------------------------------------------------------------------------
crispbd's Profile:
http://www.excelforum.com/member.php...o&userid=10880
View this thread: http://www.excelforum.com/showthread...hreadid=274567