Writing a Macro which will search for the content of a cell rather than it's value

Sears36 · #1 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!

NoSparks · #2 05-11-2017, 07:55 AM

Quote:

There must be function will allows me to search for, something like, ="=B5"

Code:

What:=Range("B5").value

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Macro to save docx to doc that checks compatibility and converts content controls to static content.	staicumihai	Word VBA	4	10-12-2016 08:23 PM
Cell Background Color: Base it on Content of Cell?	tatihulot	Excel	4	08-14-2013 03:24 PM
Looking for Windows Search app with ability to search by content	gopher_everett	Office	1	02-28-2013 09:23 PM
Writing a Macro in VBA	johnny51981	Project	1	06-22-2011 02:17 AM
Macro Writing	cweachter	Excel	0	08-09-2008 08:30 PM