Advanced Find

[Andrew XJ]

The find object(what:=) is the contents of two cells? how to perform this?
eg.
in Range("A1:Y50"), i want to find two horizontally consecutive cells that have values "value1" and "value2" respectively.

regards
Andrew XJ

 

[Dave Hawley]

Hi Andrew

Try

Sub FindThem()
Dim Rfound As Range
Dim iLoop As Integer

Set Rfound = Range("A1")
For iLoop = 1 To WorksheetFunction.CountIf(Range("A1:Y50"), "Value1")
Set Rfound = Range("A1:Y50").Find(What:="Value1", After:=Rfound, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If Rfound.Offset(0, 1) = "Value2" Then
Rfound.Range("A1:B1").Select
Exit For
End If
Next iLoop
End Sub

 

[Andrew XJ]

Thank you very much, Dave!!
I am always trying to find some stuff in the embedded VBA to solve my problem. Learning from you. I think for most problem we can use UDF to solve it.