Microsoft Office Forums - View Single Post - Writing a Macro which will search for the content of a cell rather than it's value

Sears36 · 05-11-2017, 06:49 AM

Hi,
The title may sound a little contradictory, but allow me to explain.
I need to write a large macro which, as it's starting point, will search for the contents of a cell. I say content because the value of the cell will change regularly and I need to be able to search on the new value each time I run the macro.
I tried starting a macro recording then pasting the cell's value into the Find box, however the macro only retains the specific value for which I searched (example below).

Code:

Sub TEST4()
'
' TEST4 Macro
'
'
    Range("B5").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Cells.Find(What:="ARH1", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
End Sub

There must be function will allows me to search for, something like, ="=B5"
Any help much appreciated!