Search and Replace

[Muttley]

I'm wanting to search and replace a worksheet, but rather than replace with a
set bit of text, I want to replace it with something defined within my VBA
code:

Cells.Replace What:="Annuity L_All", Replacement:=file_noxls, LookAt _
:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False

The "replace" text is definitely there, but when I run the code, it doesn't
repalce anything... can anyone help / spot the obvious error?

Thanks!

 

[Rick Rothstein]

Your code snippet works for me. Just out of curiosity, is the worksheet that
contains your "Annuity L_All" text the ActiveWorksheet (the Cells property
is not referencing any particular worksheet, so it will refer to the
ActiveSheet by default)?

 

[Jacob Skaria]

Refers ActiveSheet..

Dim file_noxls As String
file_noxls = "replaced"
Cells.Replace What:="Annuity L_All", _
Replacement:=file_noxls, SearchOrder:=xlByRows

If this post helps click Yes