find and replace multiple values in one statement

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?
 
D

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
 
K

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
 
D

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top