find and replace multiple values in one statement

[joemeshuggah]

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?

 

[Don Guillett]

Sub replacearray()
For Each c In Array("$", "-")
Selection.Replace What:=c, Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
Next c
End Sub

 

[ker_01]

You are on the right path... note that this will still process in multiple
loops (one per element in the AllChars array) so it cleans up the code but
doesn't speed up the actual processing.

HTH,
Keith

(aircode)

Sub test

AllChars = Array("$","-")

For MyChar = lbound(AllChars) to ubound(AllChars)
Selection.Replace What:=AllChars(MyChar), Replacement:="",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next
End sub

 

[Dave Peterson]

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.