+ Reply to Thread
Results 1 to 11 of 11

VBA to replace text string containing "&" in formulas

Hybrid View

  1. #1
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,669

    VBA to replace text string containing "&" in formulas

    I have following code in one of the macros.
    The purpose is to replace oldname with newname in formulas a range of cells.
    These names refer to worksheets in the excel workbook.
    The replacement is being done based on user selection via dropdown.
    So the summary sheet pulls data from selected worksheet.

    It works fine for all names except when a worksheet name contains "&".
    For example when a name is "B & G Annex", this code doesn't work.

    HTML Code: 
    Can someone suggest a workaround [besides renaming the worksheet as B and G Annex]?

    Thanks in advance for help.
    1. Click on the * Add Reputation if you think this helped you
    2. Mark your thread as SOLVED when question is resolved

    Modytrane

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    MS365 Apps for enterprise
    Posts
    5,973

    Re: VBA to replace text string containing "&" in formulas

    Can you upload sample?

    Code should work fine. Tested and did replace reference to 'Test'!A1 to 'B & G Annex'!A1 without issue.
    "Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something."
    ― Robert A. Heinlein

  3. #3
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,669

    Re: VBA to replace text string containing "&" in formulas

    Quote Originally Posted by CK76 View Post
    Can you upload sample?

    Code should work fine. Tested and did replace reference to 'Test'!A1 to 'B & G Annex'!A1 without issue.
    It does work if the newname is B & G Annex. But then it won't work next time when oldname has "&" in it.

  4. #4
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: VBA to replace text string containing "&" in formulas

    Hello modytrane,

    Why don't you just change such names in the DropDown itself?

    Regards.
    Last edited by Winon; 09-27-2019 at 09:08 AM. Reason: Spelling
    Please consider:

    Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .

  5. #5
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    MS365 Apps for enterprise
    Posts
    5,973

    Re: VBA to replace text string containing "&" in formulas

    I think the issue with not with "&" but space in old name.

    Try something like...
    oldname = IIf(InStr(Worksheets("test").Range("A1").Value, " "), "'" & Worksheets("test").Range("A1").Value & "'", Worksheets("test").Range("A1").Value)

  6. #6
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,669

    Re: VBA to replace text string containing "&" in formulas

    Quote Originally Posted by CK76 View Post
    I think the issue with not with "&" but space in old name.

    Try something like...
    oldname = IIf(InStr(Worksheets("test").Range("A1").Value, " "), "'" & Worksheets("test").Range("A1").Value & "'", Worksheets("test").Range("A1").Value)
    I realized the issue with space earlier so I have replaced space with "_" so now the name reads B_&_G_Annex.
    It still doesn't work when oldname has "&" in it. If I replace "&" with "and" it works fine.

  7. #7
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    MS365 Apps for enterprise
    Posts
    5,973

    Re: VBA to replace text string containing "&" in formulas

    I'm unable to replicate your issue. Can you upload sample workbook where you are encountering issue?

  8. #8
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,669

    Re: VBA to replace text string containing "&" in formulas

    In the attached file cell I9 has a drop down list.
    One name has "&" in it.
    Switching between all other names will change references in formulas below to correct name.
    Once you pick the "B_&_G_Annex",the references will change but after that picking any other name it won't change the formulas.

    Hope there is an easy fix.

    Thanks again for you help.
    Attached Files Attached Files

  9. #9
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    MS365 Apps for enterprise
    Posts
    5,973

    Re: VBA to replace text string containing "&" in formulas

    That's because you need to add single quote when space or & is found in the sheet name.

    So...
    Sub track()
    With Worksheets("Svc Report")
        oldname = IIf(InStr(.Range("AK1").Value, "&"), "'" & .Range("AK1").Value & "'", .Range("AK1").Value)
    End With
    newname = Worksheets("Svc Report").Range("I9").Value
    Range("E19:N44").Select
        Selection.Replace What:=oldname, Replacement:="'" & newname & "'", _
            LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
            False, ReplaceFormat:=False
        Range("C19").Select
    Range("N15").Select
        Selection.Replace What:=oldname, Replacement:="'" & newname & "'", _
            LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
            False, ReplaceFormat:=False
        Range("C19").Select
    With ActiveSheet
    .Range("AK1").Value = .Range("I9").Value
    End With
    
    End Sub

  10. #10
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,669

    Re: VBA to replace text string containing "&" in formulas

    Thank you so much.
    I should've known. I was adding single quote to newname but didn't do it for oldname.

    I appreciate your help.

  11. #11
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    MS365 Apps for enterprise
    Posts
    5,973

    Re: VBA to replace text string containing "&" in formulas

    You are welcome, and thanks for the rep

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 5
    Last Post: 02-05-2019, 12:03 AM
  2. [SOLVED] retrieve the entire text from the "[(" ")]" from string
    By alexgoaga in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-14-2018, 03:59 AM
  3. Replies: 3
    Last Post: 05-02-2018, 08:03 AM
  4. [SOLVED] Need formula that finds the last ", " in a text string & substitutes it w/ the word "and"
    By danielneedssomehelp in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 08-23-2014, 04:06 PM
  5. Replies: 1
    Last Post: 08-15-2014, 06:00 AM
  6. Replies: 0
    Last Post: 03-21-2014, 09:58 AM
  7. Replace numbers with Text ("1" to "Apple", for example)
    By pilotwings64 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-07-2008, 06:56 PM

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