Macro to replace * with /

[marston.gould]

Well here's a rather embarassing problem.

I sent out about a kazillion files for people to enter information in
and one of the columns has a =Cell*12 in it where cell is the cell
address over dozens of rows (e.g.

=aa10*12
=aa11*12
etc.

I thought it would be simple to do a record macro to replace the * with
a / but something that I thought would be trival has escaped me....
any help would be appreciated.

 

[Tom Ogilvy]

to replace *, you would use ~*

Sub Macro5()
Cells.Replace What:="~*", Replacement:="/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

 

[kkknie]

Excel hates searching for a wild card...

One way I know is to create a macro to do it. In the VB editor, inser
a module and enter the following code:

Sub Repl()

Dim c As Range
Application.ScreenUpdating = False
For Each c In ActiveSheet.UsedRange
c.Formula = Replace(c.Formula, Chr(42), "/")
Next

End Sub

Then from the sheet you wish to search and replace, select Tools
Macro --> Macros and then select that macro from the list.

Don't know of a better way... Sorry.

 

[kkknie]

Aha!

You don't even need to write code. Just type in ~* in the find and
in the replace for a find/replace dialog (with all cells selected).

Is that escape character documented anywhere???

 

[marston.gould]

I needed code because there are something like 1200 files that are
already in the hot hands of others that I needed to be able to make the
change to. Using a macro allows me to send a folks a method that can
automatically open their files and make the change. Not to mention,
that doing this to 1200 files by hand would have been tiresome