find and replace

[ph03]

Is there a Excel VBA code to seach and replace? I'd like to select all; find "-1"; and replace with "n/a". Thanks

 

[Jonmo1]

Welcome to the board...

Sure, the Macro Recorder is your friend...

In Xl2003, click tools - Macro - Record new macro
Give it a name and click OK

Then do the Find/Replace as you normally would by hand.
When finished, click Tools - Macro - Stop Recording

You will now have some code to work with, giving you basic syntax example.


In 2007 or higher, the Macro Recorder is on the Developer's tab.

 

[ph03]

Thanks. This was helpful.

 

[Rick Rothstein]

Assuming -1 is the cell's entire contents (and that it is not surrounded with quote marks), then you can change them all at once with this line of code...

Cells.Replace -1, "n/a", xlWhole