find and replace macro strange behaviour
 

I've tested this procedure :

1- format a cell in text

2- copy the below formula in the cell

=IF(26;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 ???!!!!

tx

nic

find and replace macro strange behaviour
 

Your code worked fine for me. I used xl2003 to test.

What version of excel are you using?

I seem to recall in earlier versions if I had a cell that was formatted as text
that looked like a formula (started with an equal sign)

=Header1=
or
=========

Then when I did that kind of mass change, excel wanted to convert those cells to
real formulas. They'd either convert to nonsense (lots of #name? errors) or the
macro would yell at me that the formula wasn't correct.

Is it possible that you have this problem?

If it is, maybe you could limit your replacement to just the cells with
formulas. (I'm guessing that you're trying to get the workbook to recalculate.)

Option Explicit
Sub remplacement()

Dim myRng As Range

With Sheets("Sheet2")
Set myRng = Nothing
On Error Resume Next
Set myRng = .Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "No formulas found"
Exit Sub
End If

myRng.Replace What:="=", replacement:="=", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False

End With
End Sub



But maybe I'm remembering incorrectly?????




Nicawette wrote:

I've tested this procedure :

1- format a cell in text

2- copy the below formula in the cell

=IF(26;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 ???!!!!

tx

nic

--

Dave Peterson

find and replace macro strange behaviour
 

Please ignore this message. I didn't see the first step--format the cell as
Text.

But it looks like you have a good response at your other post.

Dave Peterson wrote:

Your code worked fine for me. I used xl2003 to test.

What version of excel are you using?

I seem to recall in earlier versions if I had a cell that was formatted as text
that looked like a formula (started with an equal sign)

=Header1=
or
=========

Then when I did that kind of mass change, excel wanted to convert those cells to
real formulas. They'd either convert to nonsense (lots of #name? errors) or the
macro would yell at me that the formula wasn't correct.

Is it possible that you have this problem?

If it is, maybe you could limit your replacement to just the cells with
formulas. (I'm guessing that you're trying to get the workbook to recalculate.)

Option Explicit
Sub remplacement()

Dim myRng As Range

With Sheets("Sheet2")
Set myRng = Nothing
On Error Resume Next
Set myRng = .Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "No formulas found"
Exit Sub
End If

myRng.Replace What:="=", replacement:="=", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False

End With
End Sub

But maybe I'm remembering incorrectly?????

Nicawette wrote:

I've tested this procedure :

1- format a cell in text

2- copy the below formula in the cell

=IF(26;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 ???!!!!

tx

nic

--

Dave Peterson

--

Dave Peterson

find and replace macro strange behaviour
 

Dear Dave Thank you for all.

Nic


Dave Peterson wrote:
Please ignore this message. I didn't see the first step--format the cell as
Text.

But it looks like you have a good response at your other post.

Dave Peterson wrote:

Your code worked fine for me. I used xl2003 to test.

What version of excel are you using?

I seem to recall in earlier versions if I had a cell that was formatted as text
that looked like a formula (started with an equal sign)

=Header1=
or
=========

Then when I did that kind of mass change, excel wanted to convert those cells to
real formulas. They'd either convert to nonsense (lots of #name? errors) or the
macro would yell at me that the formula wasn't correct.

Is it possible that you have this problem?

If it is, maybe you could limit your replacement to just the cells with
formulas. (I'm guessing that you're trying to get the workbook to recalculate.)

Option Explicit
Sub remplacement()

Dim myRng As Range

With Sheets("Sheet2")
Set myRng = Nothing
On Error Resume Next
Set myRng = .Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "No formulas found"
Exit Sub
End If

myRng.Replace What:="=", replacement:="=", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False

End With
End Sub

But maybe I'm remembering incorrectly?????

Nicawette wrote:

I've tested this procedure :

1- format a cell in text

2- copy the below formula in the cell

=IF(26;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 ???!!!!

tx

nic

--

Dave Peterson

--

Dave Peterson