Macro to Replace only the first time a Word apears in cells ina column?

[tonywatsonhelp]

Hi Everyone,

I've been trying to do a simple find and replace down Column AQ but i'm running into problems because sometime the words i'm trying to replace are pearing again. in the same cell

So wjay I what to do is find "What Time Started" and replace with "Begin Time".

the problem is the cells might have two or three lots of "What Time Started"? can I get it to only replace the first One?

Tony

 

[footoo]

Dim rng$
rng = Range([AQ1], Cells(Rows.Count, "AQ").End(xlUp)).Address
Range(rng) = Evaluate("substitute(" & rng & ",""What Time Started"", ""Begin Time"",1)")

 

[tonywatsonhelp]

brilliant footoo,
thank you so much

Tony

 

[tonywatsonhelp]

Oh Dear,
Sorry Footoo, but I've just realised this is not working
it is simply taking the data in column AQ1 replacing and coping it down the page>
I need a macro to replace the first example of the word in each row

can anyone help please

Tony

 

[Peter_SSs]

can anyone help please

Small set of sample data and expected results?

 

[footoo]

Oh Dear,
Sorry Footoo, but I've just realised this is not working
it is simply taking the data in column AQ1 replacing and coping it down the page>
I need a macro to replace the first example of the word in each row

can anyone help please

Tony

Sorry. Should have tested it. Try this :

Dim rng$
rng = Range([AQ1], Cells(Rows.Count, "AQ").End(xlUp)).Address
Range(rng) = Evaluate("INDEX(substitute(" & rng & ",""What Time Started"", ""Begin Time"",1),)")