Replace a spreadsheets named cells/ranges with exact cell address.

I have a named range (A1:A6) which is called MyRange. Lets say each cell is
populated with a number A1 = 1, A2 = 2. A3 = 3 etc. In the row (B1:B6)
beneath it, I have entered =MyRange in each cell. As such the values in
B1:B6 mirror those in A1:A6. That is, even though I use MyRange in each cell
in Row B, Excel knows which cell to pull from in rowA to correctly populate
the cells in Row B.

Now If I use the Macro below to delete the named ranges I get the following
range in each of the RowB cells =A1:A6. Once again Excel knows which cell to
pull from in Row A to populate cells in Row B.

My question is how can I adjust the macro below so that it deletes the named
range but replaces it with eact cell address that is being pulled into Row B.
So in B2 I do not want to see =A1:A6 I want to see =A2.

What I am looking to do here is replace a spreadsheets named cells/ranges
with exact cell address.

In fact I think Microsoft should proivde switch in Excel to allow the user
to do this!!!!

Sub DenameFormulas()
'This code replaces named cells with actuall address behind them
Dim Cell As Range
ActiveSheet.TransitionFormEntry = True
For Each Cell In Cells.SpecialCells(xlFormulas)
Cell.Formula = Cell.Formula
Next
ActiveSheet.TransitionFormEntry = False
End Sub

Thanks

It seems to me that you are trying to hit a moving target with permanent
changes. Instead I'm going to take a guess on the following sentence.

> In fact I think Microsoft should provide switch in Excel to allow the user
> to do this!!!!

You can toggle into and out of the formula view (tools, options, view, formulas)
using Ctrl+` (accent grave) or Ctrl+ whatever key is to the left of the number row.
http://www.mvps.org/dmcritchie/excel/shortx2k.htm

If that was what you really wanted then you might also be interested in the
following:
http://www.mvps.org/dmcritchie/excel...htm#GetFormula
http://www.mvps.org/dmcritchie/excel...FormulaExample
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"ExcelMonkey" <[email protected]> wrote in message news:[email protected]...
> I have a named range (A1:A6) which is called MyRange. Lets say each cell is
> populated with a number A1 = 1, A2 = 2. A3 = 3 etc. In the row (B1:B6)
> beneath it, I have entered =MyRange in each cell. As such the values in
> B1:B6 mirror those in A1:A6. That is, even though I use MyRange in each cell
> in Row B, Excel knows which cell to pull from in rowA to correctly populate
> the cells in Row B.
>
> Now If I use the Macro below to delete the named ranges I get the following
> range in each of the RowB cells =A1:A6. Once again Excel knows which cell to
> pull from in Row A to populate cells in Row B.
>
> My question is how can I adjust the macro below so that it deletes the named
> range but replaces it with eact cell address that is being pulled into Row B.
> So in B2 I do not want to see =A1:A6 I want to see =A2.
>
> What I am looking to do here is replace a spreadsheets named cells/ranges
> with exact cell address.
>
> In fact I think Microsoft should proivde switch in Excel to allow the user
> to do this!!!!
>
> Sub DenameFormulas()
> 'This code replaces named cells with actuall address behind them
> Dim Cell As Range
> ActiveSheet.TransitionFormEntry = True
> For Each Cell In Cells.SpecialCells(xlFormulas)
> Cell.Formula = Cell.Formula
> Next
> ActiveSheet.TransitionFormEntry = False
> End Sub
>
> Thanks