Thread: cell.replace strange behaviour

Hi all,

Sub remplacement()
Sheets("Sheet2").Select
Cells.Replace What:="=", replacement:="=", LookAt:=xlPart,
SearchOrder _
:=xlByRows, MatchCase:=False
Sheets("Sheet3").Select
Cells.Replace What:="=", replacement:="=", LookAt:=xlPart,
SearchOrder _
:=xlByRows, MatchCase:=False
End Sub

I've recorded this macro to avoid hitting enter each time when I copy a
formula located in a cell in text format into a new cell (not in text
format).

When I run this macro it works fine with a formula like "=x+5" or
"=len()" but not with a formula like "=vlookup()" I have an error 1004
message saying that "replace method of range class failed".

The amazing thing is that when I have recorded this macro, it worked
fine with the =vlookup() and also worked fine when I've used the find
and replace ????

Any idea to solve it ?

Tx

Nic.

there is apparently something wrong with the formula that you are trying to
do the replace on when it errors.

there is no other reason that I can think of that this should not work.

--
Regards,
Tom Ogilvy


"Nicawette" wrote:

> Hi all,
>
> Sub remplacement()
> Sheets("Sheet2").Select
> Cells.Replace What:="=", replacement:="=", LookAt:=xlPart,
> SearchOrder _
> :=xlByRows, MatchCase:=False
> Sheets("Sheet3").Select
> Cells.Replace What:="=", replacement:="=", LookAt:=xlPart,
> SearchOrder _
> :=xlByRows, MatchCase:=False
> End Sub
>
> I've recorded this macro to avoid hitting enter each time when I copy a
> formula located in a cell in text format into a new cell (not in text
> format).
>
> When I run this macro it works fine with a formula like "=x+5" or
> "=len()" but not with a formula like "=vlookup()" I have an error 1004
> message saying that "replace method of range class failed".
>
> The amazing thing is that when I have recorded this macro, it worked
> fine with the =vlookup() and also worked fine when I've used the find
> and replace ????
>
> Any idea to solve it ?
>
> Tx
>
> Nic.
>
>

ok tx for your help

I will retest it in a more accurate way

Nic

Tom Ogilvy a =E9crit :

> there is apparently something wrong with the formula that you are trying =
to
> do the replace on when it errors.
>
> there is no other reason that I can think of that this should not work.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Nicawette" wrote:
>
> > Hi all,
> >
> > Sub remplacement()
> > Sheets("Sheet2").Select
> > Cells.Replace What:=3D"=3D", replacement:=3D"=3D", LookAt:=3DxlPart,
> > SearchOrder _
> > :=3DxlByRows, MatchCase:=3DFalse
> > Sheets("Sheet3").Select
> > Cells.Replace What:=3D"=3D", replacement:=3D"=3D", LookAt:=3DxlPart,
> > SearchOrder _
> > :=3DxlByRows, MatchCase:=3DFalse
> > End Sub
> >
> > I've recorded this macro to avoid hitting enter each time when I copy a
> > formula located in a cell in text format into a new cell (not in text
> > format).
> >
> > When I run this macro it works fine with a formula like "=3Dx+5" or
> > "=3Dlen()" but not with a formula like "=3Dvlookup()" I have an error 1=
004
> > message saying that "replace method of range class failed".
> >
> > The amazing thing is that when I have recorded this macro, it worked
> > fine with the =3Dvlookup() and also worked fine when I've used the find
> > and replace ????
> >=20
> > Any idea to solve it ?
> >=20
> > Tx
> >=20
> > Nic.
> >=20
> >

I've tested this procedure :

1- format a cell in text

2- copy the below formula in the cell

=3DIF(2>6;TRUE;FALSE)

4- change the format of the cell into general

3- run this macro

Sub remplacement()
Sheets("Sheet2").Select
Cells.Replace What:=3D"=3D", replacement:=3D"=3D", LookAt:=3DxlPart,
SearchOrder _
:=3DxlByRows, MatchCase:=3DFalse
End Sub

and I've an error : "replace method of range class failed" ?

BUT if I do a "Find and replace" it works, If I edit the cell and hit
enter it works, the formula give the value "FALSE"

is it an excel bug ? why this macros doesn't work, this maccro has been
recorded with the "find and replace" menu, it has to be the same way of
doing ???!!!!



Sub remplacement()
> > > Sheets("Sheet2").Select
> > > Cells.Replace What:=3D"=3D", replacement:=3D"=3D", LookAt:=3DxlPa=
rt,
> > > SearchOrder _
> > > :=3DxlByRows, MatchCase:=3DFalse
> > > Sheets("Sheet3").Select
> > > Cells.Replace What:=3D"=3D", replacement:=3D"=3D", LookAt:=3DxlPa=
rt,
> > > SearchOrder _
> > > :=3DxlByRows, MatchCase:=3DFalse
> > > End Sub


Nicawette a =E9crit :

> ok tx for your help
>
> I will retest it in a more accurate way
>
> Nic
>
> Tom Ogilvy a =E9crit :
>
> > there is apparently something wrong with the formula that you are tryin=
g to
> > do the replace on when it errors.
> >
> > there is no other reason that I can think of that this should not work.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Nicawette" wrote:
> >
> > > Hi all,
> > >
> > > Sub remplacement()
> > > Sheets("Sheet2").Select
> > > Cells.Replace What:=3D"=3D", replacement:=3D"=3D", LookAt:=3DxlPa=
rt,
> > > SearchOrder _
> > > :=3DxlByRows, MatchCase:=3DFalse
> > > Sheets("Sheet3").Select
> > > Cells.Replace What:=3D"=3D", replacement:=3D"=3D", LookAt:=3DxlPa=
rt,
> > > SearchOrder _
> > > :=3DxlByRows, MatchCase:=3DFalse
> > > End Sub
> > >
> > > I've recorded this macro to avoid hitting enter each time when I copy=
a
> > > formula located in a cell in text format into a new cell (not in text
> > > format).
> > >
> > > When I run this macro it works fine with a formula like "=3Dx+5" or
> > > "=3Dlen()" but not with a formula like "=3Dvlookup()" I have an error=
1004
> > > message saying that "replace method of range class failed".
> > >
> > > The amazing thing is that when I have recorded this macro, it worked
> > > fine with the =3Dvlookup() and also worked fine when I've used the fi=
nd
> > > and replace ????
> > >
> > > Any idea to solve it ?
> > >=20
> > > Tx
> > >=20
> > > Nic.
> > >=20
> > >

Apparently you are not in the US. I believe the problem is the semicolon in
your formula - once VBA is involved (as it is in this case), it is expecting
the formula to be in US english notation. So it doesn't see the formula as
being valid. Test your formula with commas instead of semicolons to confirm.

A workaround might be
If you don't have any working formulas in the sheet, try

cells.Numberformat = "general"
cells.copy
cells.PasteSpecial xlValues

--
Regards,
Tom Ogilvy


"Nicawette" wrote:

> I've tested this procedure :
>
> 1- format a cell in text
>
> 2- copy the below formula in the cell
>
> =IF(2>6;TRUE;FALSE)
>
> 4- change the format of the cell into general
>
> 3- run this macro
>
> Sub remplacement()
> Sheets("Sheet2").Select
> Cells.Replace What:="=", replacement:="=", LookAt:=xlPart,
> SearchOrder _
> :=xlByRows, MatchCase:=False
> End Sub
>
> and I've an error : "replace method of range class failed" ?
>
> BUT if I do a "Find and replace" it works, If I edit the cell and hit
> enter it works, the formula give the value "FALSE"
>
> is it an excel bug ? why this macros doesn't work, this maccro has been
> recorded with the "find and replace" menu, it has to be the same way of
> doing ???!!!!
>
>
>
> Sub remplacement()
> > > > Sheets("Sheet2").Select
> > > > Cells.Replace What:="=", replacement:="=", LookAt:=xlPart,
> > > > SearchOrder _
> > > > :=xlByRows, MatchCase:=False
> > > > Sheets("Sheet3").Select
> > > > Cells.Replace What:="=", replacement:="=", LookAt:=xlPart,
> > > > SearchOrder _
> > > > :=xlByRows, MatchCase:=False
> > > > End Sub
>
>
> Nicawette a écrit :
>
> > ok tx for your help
> >
> > I will retest it in a more accurate way
> >
> > Nic
> >
> > Tom Ogilvy a écrit :
> >
> > > there is apparently something wrong with the formula that you are trying to
> > > do the replace on when it errors.
> > >
> > > there is no other reason that I can think of that this should not work.
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > > "Nicawette" wrote:
> > >
> > > > Hi all,
> > > >
> > > > Sub remplacement()
> > > > Sheets("Sheet2").Select
> > > > Cells.Replace What:="=", replacement:="=", LookAt:=xlPart,
> > > > SearchOrder _
> > > > :=xlByRows, MatchCase:=False
> > > > Sheets("Sheet3").Select
> > > > Cells.Replace What:="=", replacement:="=", LookAt:=xlPart,
> > > > SearchOrder _
> > > > :=xlByRows, MatchCase:=False
> > > > End Sub
> > > >
> > > > I've recorded this macro to avoid hitting enter each time when I copy a
> > > > formula located in a cell in text format into a new cell (not in text
> > > > format).
> > > >
> > > > When I run this macro it works fine with a formula like "=x+5" or
> > > > "=len()" but not with a formula like "=vlookup()" I have an error 1004
> > > > message saying that "replace method of range class failed".
> > > >
> > > > The amazing thing is that when I have recorded this macro, it worked
> > > > fine with the =vlookup() and also worked fine when I've used the find
> > > > and replace ????
> > > >
> > > > Any idea to solve it ?
> > > >
> > > > Tx
> > > >
> > > > Nic.
> > > >
> > > >
>
>

Hi tom,

I'm using excel 2000 and you are right I'm not in the US. Unfortunately
changing semicolons into commas gives me an error. Anyway I will try a
workaround.

Tx a lot for your kind help


Tom Ogilvy wrote:
> Apparently you are not in the US. I believe the problem is the semicolon=
in
> your formula - once VBA is involved (as it is in this case), it is expect=
ing
> the formula to be in US english notation. So it doesn't see the formula =
as
> being valid. Test your formula with commas instead of semicolons to conf=
irm.
>
> A workaround might be
> If you don't have any working formulas in the sheet, try
>
> cells.Numberformat =3D "general"
> cells.copy
> cells.PasteSpecial xlValues
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Nicawette" wrote:
>
> > I've tested this procedure :
> >
> > 1- format a cell in text
> >
> > 2- copy the below formula in the cell
> >
> > =3DIF(2>6;TRUE;FALSE)
> >
> > 4- change the format of the cell into general
> >
> > 3- run this macro
> >
> > Sub remplacement()
> > Sheets("Sheet2").Select
> > Cells.Replace What:=3D"=3D", replacement:=3D"=3D", LookAt:=3DxlPart,
> > SearchOrder _
> > :=3DxlByRows, MatchCase:=3DFalse
> > End Sub
> >
> > and I've an error : "replace method of range class failed" ?
> >
> > BUT if I do a "Find and replace" it works, If I edit the cell and hit
> > enter it works, the formula give the value "FALSE"
> >
> > is it an excel bug ? why this macros doesn't work, this maccro has been
> > recorded with the "find and replace" menu, it has to be the same way of
> > doing ???!!!!
> >
> >
> >
> > Sub remplacement()
> > > > > Sheets("Sheet2").Select
> > > > > Cells.Replace What:=3D"=3D", replacement:=3D"=3D", LookAt:=3D=
xlPart,
> > > > > SearchOrder _
> > > > > :=3DxlByRows, MatchCase:=3DFalse
> > > > > Sheets("Sheet3").Select
> > > > > Cells.Replace What:=3D"=3D", replacement:=3D"=3D", LookAt:=3D=
xlPart,
> > > > > SearchOrder _
> > > > > :=3DxlByRows, MatchCase:=3DFalse
> > > > > End Sub
> >
> >
> > Nicawette a =E9crit :
> >
> > > ok tx for your help
> > >
> > > I will retest it in a more accurate way
> > >
> > > Nic
> > >
> > > Tom Ogilvy a =E9crit :
> > >
> > > > there is apparently something wrong with the formula that you are t=
rying to
> > > > do the replace on when it errors.
> > > >
> > > > there is no other reason that I can think of that this should not w=
ork.
> > > >
> > > > --
> > > > Regards,
> > > > Tom Ogilvy
> > > >
> > > >
> > > > "Nicawette" wrote:
> > > >
> > > > > Hi all,
> > > > >
> > > > > Sub remplacement()
> > > > > Sheets("Sheet2").Select
> > > > > Cells.Replace What:=3D"=3D", replacement:=3D"=3D", LookAt:=3D=
xlPart,
> > > > > SearchOrder _
> > > > > :=3DxlByRows, MatchCase:=3DFalse
> > > > > Sheets("Sheet3").Select
> > > > > Cells.Replace What:=3D"=3D", replacement:=3D"=3D", LookAt:=3D=
xlPart,
> > > > > SearchOrder _
> > > > > :=3DxlByRows, MatchCase:=3DFalse
> > > > > End Sub
> > > > >
> > > > > I've recorded this macro to avoid hitting enter each time when I =
copy a
> > > > > formula located in a cell in text format into a new cell (not in =
text
> > > > > format).
> > > > >
> > > > > When I run this macro it works fine with a formula like "=3Dx+5" =
or
> > > > > "=3Dlen()" but not with a formula like "=3Dvlookup()" I have an e=
rror 1004
> > > > > message saying that "replace method of range class failed".
> > > > >
> > > > > The amazing thing is that when I have recorded this macro, it wor=
ked
> > > > > fine with the =3Dvlookup() and also worked fine when I've used th=
e find
> > > > > and replace ????
> > > > >
> > > > > Any idea to solve it ?
> > > > >
> > > > > Tx
> > > > >=20
> > > > > Nic.
> > > > >=20
> > > > >
> >=20
> >