View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Dave Peterson Dave Peterson is offline
external usenet poster
 
Posts: 35,218
Default find and replace multiple values in one statement

I'd use:

Dim myChars as Variant
dim cCtr as long

mychars = array("$", "-")

for cctr = lbound(mychars) to ubound(mychars)
selection.replace what:=mychars(cctr), replacement:="", .....
next cctr

Be careful. That $ could be used in formulas, too:
=$a$1*c99

Not sure if that's a problem.

joemeshuggah wrote:

is it possible to do a find and replace with one statement using a variable?
for example instead of:

Selection.Replace What:="$", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="-", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

is it possible to use

Selection.Replace What:=MyChar, Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

instead?

or can you not assign more than one character to a variable?


--

Dave Peterson