find and replace multiple values in one statement

  • Thread starter Thread starter joemeshuggah
  • Start date Start date
J

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?
 
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
 
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
 
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.
 
Back
Top