replace in vba

[grt]

What am i doing wrong. The following line keeps giving me an error 1004.
Strange thing is that it used to work until just a few minutes ago.

rng1grt.Replace what:="C", replacement:="1", Lookat:=xlPart, _
searchorder:=xlByRows, MatchCase:=False

rgds,

grt

 

[JE McGimpsey]

What am i doing wrong. The following line keeps giving me an error 1004.
Strange thing is that it used to work until just a few minutes ago.

rng1grt.Replace what:="C", replacement:="1", Lookat:=xlPart, _
searchorder:=xlByRows, MatchCase:=False

rgds,

First - what version of XL?

There's nothing wrong with the code itself. What did you change a few
minutes ago?

One way to cause the error is to have a formula in rng1grt that contains
a C, e.g.:

=CHOOSE(B1,"A","B","D","E")


WHen XL tries to replace the C in CHOOSE with a 1 a syntax error results
that throws a run-time 1004 error.

One workaround:

rng1grt.SpecialCells(xlCellTypeConstants, xlTextValues).Replace _
What:="C", _
Replacement:=1, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False

which will ignore formulae and numbers.

 

[grt]

-----Original Message-----

First - what version of XL?

There's nothing wrong with the code itself. What did you change a few
minutes ago?

One way to cause the error is to have a formula in rng1grt that contains
a C, e.g.:

=CHOOSE(B1,"A","B","D","E")


WHen XL tries to replace the C in CHOOSE with a 1 a syntax error results
that throws a run-time 1004 error.

One workaround:

rng1grt.SpecialCells(xlCellTypeConstants, xlTextValues).Replace _
What:="C", _
Replacement:=1, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False

which will ignore formulae and numbers.
.

thanks for your helpful reply. I am using Excel X for Mac. I indeed did put a
formula in the range that partly needed to be replaced. I ammended the
range so it works fine again. With your workaround I might change it back to
the old layout.
rgds