Results 1 to 8 of 8
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    replace names inside formula (2003 / SP2)

    I have a workbook which contains many overlapping range names. I used Name Manager to identify these names but I don't know how to resolve this such that no names are overlapping. Excel happily allows overlapping names but Xcelsius doesn't and that's why I need to replace them.

    Example Names: Data1 overlaps Data11, Data1 overlaps Data22, Data1 overlaps Data33 (this is the type of list I see in Name Manager, vers4.1 build 601)
    Data1 refers to something like pricing!$A$12:$A$15 and each of the overlapped names contain this set of cells in their own defined name.

    Is it some combo of Find/Replace? I can't quite figure it out.

    Deb <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: replace names inside formula (2003 / SP2)

    What do you want to do? Delete any defined names whose range overlaps with that of other ones? You could write VBA code for that, using a double loop through the collection of names.
    Or do you want to adjust the ranges? That could become quite hairy, I think.

  3. #3
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: replace names inside formula (2003 / SP2)

    I'd like to keep the names but I think I'll have to replace all of them with their addresses where they are used in formulas. So if I have a formula that is "=PriceMax*.2" then I need to replace PriceMax with $B$85 (or whatever that name refers to). Can I do this w/o VBA? I have 167 of these overlapping names. I can do it semi-manually by jotting down the 'refers to' for each name and then do a find/replace throughout the workbook. Was hoping for a bit more automation or that Name Manager had a way to remove the names in the workbook with what they refer to.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: replace names inside formula (2003 / SP2)

    Perhaps you can use this macro as starting point.
    <code>
    Sub UnuseNames()
    Dim nme As Name
    Dim wsh As Worksheet
    Dim rng As Range
    Dim strName As String
    Dim strRefersTo As String
    For Each nme In ActiveWorkbook.Names
    strName = nme.Name
    strRefersTo = Mid(nme.RefersTo, 2)
    For Each wsh In ActiveWorkbook.Worksheets
    For Each rng In wsh.UsedRange.SpecialCells(xlCellTypeFormulas).Cel ls
    rng.Formula = Replace(rng.Formula, strName, strRefersTo)
    Next rng
    Next wsh
    Next nme
    End Sub
    </code>
    I have only tested it in a simple workbook. It doesn't handle names that refer to other names correctly. I'd test extensively before using it on a 'production' workbook.

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: replace names inside formula (2003 / SP2)

    Your code has a snag: it will also try to replace Name1 where the formula in fact contains Name11.

    There is a trick to unapply names, but is non-discriminate: it does them all. I forgot how, but it had something to do with excel's transition formula evaluation settings and then doing a search and replace or something like it.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: replace names inside formula (2003 / SP2)

    Good point! I also failed to take into account that there might be sheets without formulas.

    I found the trick you refer to: Can I "De-Name" Formula Cell References?.

    Here is a version that loops through all sheets:
    <code>
    Sub UnuseNames()
    Dim wsh As Worksheet
    Dim rng As Range
    Dim cell As Range
    On Error Resume Next
    For Each wsh In ActiveWorkbook.Worksheets
    Set rng = wsh.UsedRange.SpecialCells(xlCellTypeFormulas)
    If Err = 0 Then
    wsh.TransitionFormEntry = True
    For Each cell In rng.Cells
    cell.Formula = cell.Formula
    Next cell
    wsh.TransitionFormEntry = False
    Else
    Err.Clear
    End If
    Next wsh
    End Sub
    </code>
    Again, I only tested in a simple workbook.

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: replace names inside formula (2003 / SP2)

    I haven't tried, but I suspect you could do all formulas in one go:

    With wsh.usedrange.specialcells(xlCellTypeFormulas)
    .Formula=.Formula
    End With
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: replace names inside formula (2003 / SP2)

    Yes, that works, thanks. The simplified macro becomes
    <code>
    Sub UnuseNames()
    Dim wsh As Worksheet
    Dim rng As Range
    On Error Resume Next
    For Each wsh In ActiveWorkbook.Worksheets
    wsh.TransitionFormEntry = True
    With wsh.UsedRange.SpecialCells(xlCellTypeFormulas)
    .Formula = .Formula
    End With
    wsh.TransitionFormEntry = False
    Next wsh
    End Sub
    </code>
    Note: this code replaces defined names in formulas with relative references, even if the names have been defined with absolute references.

Posting Permissions

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