Thread: [SOLVED] Excel Macro does not Replace text in formulas for several workshee

Dear all, having tried to REPLACE "=" with "&" (to inactivate and activate an
add-in formula) in two different worksheets of the same workbook, the
"replace function" (see below macro) only works in the first (active)
worksheet. Suggestions?:

Sub Macro1()
Columns("B:E").Select
Selection.Replace What:="=INF", Replacement:="&INF", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets("Sheet 2").Select
Application.Goto Reference:="Area2"
Selection.Replace What:="&INF", Replacement:="=INF", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets("Sheet 1").Select
MsgBox ("Ready")
End Sub

It might be that "activating" the formula (chaning to "=") demands some
processing time, and a "wait" command is needed. However it seems my macro is
not even moving to sheet 2.

Kind regards /Jan

Have you tried single-stepping through the macro to discover what is
happening? You seem uncertain as to whether it reaches the line:

Sheets("Sheet 2").Select

Hope this helps.

Pete

Yes thanks, It reaches the second sheet. It seems more like the replace
function is not working properly when i try to shift from "=function(a,b,c)"
to "&function(a,b,c)", the "&" only to stop calculation of an add-in
function.
But if I instead try with replacing "=function(a,b,c)" by "=ffffff(a,b,c)",
sort of keeping a formula intact starting with "=", the replace function seem
to work better
Rgds /Jan

"Pete_UK" wrote:

> Have you tried single-stepping through the macro to discover what is
> happening? You seem uncertain as to whether it reaches the line:
>
> Sheets("Sheet 2").Select
>
> Hope this helps.
>
> Pete
>
>