Macro Substitution (runn string as a command)

[mdav2]

I am trying to run a simple command which didn't work

Cells.Find(What:=Chr(34) + Trim(strRef) + Chr(34), After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate

Error: Object variable or with block not set

I then thought this might run as a macro substition (EG build the string then execute it):

strcommand = "Cells.Find(What:=" + Chr(34) + Trim(strRef) + Chr(34) + ", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate"
???? run StrCommand ????

However, for the life of me I cannot remember how the hell to do this. I don't understand why you can't substitute a variable into a command to force me to look at doing this anyway. Can someone put me out of my misery.

Thanks

Mark Davies
Warwickshire County Council

 

[fumei]

Is strRef actually given a value? Is it even declared properly?

Further, Cells.Find applies to a range. If you have not given it a range, then..."Error: Object variable or with block not set". Have you given it a range? If so, please post more expanded code so we can see if we can narrow things down.

Gerry

 

[TonyJollans]

If what you are looking for is not found, then you will not get a Range returned - technically an "Object variable not set" - and will not be able to Activate it.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website