+ Reply to Thread
Results 1 to 3 of 3

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

  1. #1
    /Jan
    Guest

    [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

  2. #2
    Pete_UK
    Guest

    Re: Excel Macro does not Replace text in formulas for several workshee

    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


  3. #3
    /Jan
    Guest

    Re: Excel Macro does not Replace text in formulas for several work

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


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1