find and replace multiple values in one statement
 

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?

find and replace multiple values in one statement
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"joemeshuggah" wrote in message
...
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?

find and replace multiple values in one statement
 

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


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

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