Finding named range references in formulae

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

Guest

This post is similar to my post from yesterday about replacing references to
named ranges with their respective addresses, but now I have a different
question.

While using Jan Karel Pieterse's Name Manager, I found that it was able to
rename Named Ranges and replace references to them in cell formulae.

It was also able to replace references to that range. For example, I had
ranges named "i" and "int_x" and a cell containing the formula
"=sum(i,int_x)". When I told Name Manager to rename "i" to "go", it replaced
the stand-alone "i" in the formula and NOT the "i" in "int_x".

Is there some method that simplifies this task? I was trying to using
string functions to replace the "i" with "go", but that resulted in "gont_x".
Is there a method of looking in a cell's formula and finding the references,
similiar to how we look at a formula in the formula bar and see the
references' text colored and their cells highlighted?

My overall goal is to write a routine that removes all named ranges from a
workbook and replaces all references with the named range's address.

Thanks,
Pflugs
 
For named ranges in formulas in worksheets...

I'd do this against a copy of the file...

Jim Rech posted a nice response at:
http://groups.google.com/groups?threadm=u3ZAo#FmAHA.2048@tkmsftngp03

From: Jim Rech ([email protected])
Subject: Re: Can I "De-Name" Formula Cell References?
Newsgroups: microsoft.public.excel.misc, microsoft.public.excel
Date: 2001-02-16 13:32:51 PST

To do it to a cell or two first turn on Transition Formula Entry under
Tools, Options, Transition. Then go to the cell and press F2 and Enter.
When you turn off TFE the formula references should be de-named.

If you have a lot of cells to de-name select the range and run this macro:

Sub Dename()
Dim Cell As Range
ActiveSheet.TransitionFormEntry = True
For Each Cell In Selection.SpecialCells(xlFormulas)
Cell.Formula = Cell.Formula
Next
ActiveSheet.TransitionFormEntry = False
End Sub

--
Jim Rech
Excel MVP

====
Be aware that any reference to those names in your code will be broken.
 
Dave,

Thanks very much for the help. I wasn't aware of Google Groups for Excel,
and I will be sure to check that before posting. Once again, I'm amazed at
the simple solution to a difficult problem.

Pflugs
 
Back
Top