Excel Macro does not Replace text in formulas for several workshee

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Back
Top