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

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...el.programming

In a standard module: Not perfect, so have a backup:

'================================================
Option Explicit

Sub ReplaceNamesWithRefs()
' Loop counter
Dim i As Integer
Dim szWhat As String
Dim szReplace As String
Dim wks As Worksheet
Dim rRng As Range

i = 1

On Error Resume Next
' Loop through all names in the workbook
Dim nm As Name
For Each nm In ThisWorkbook.Names


' Store each name object in a variable
szWhat = ThisWorkbook.Names(i).Name


' In case we find cells that match the defined name's name
For Each rRng In Cells.SpecialCells(xlCellTypeConstants)
If rRng.Value = szWhat Then Goto NameMatch
Next rRng


' Store each names RefersTo value in a variable that trims the
"=" sign
szReplace = Replace(ThisWorkbook.Names(i).RefersTo, "=", Empty)


'
===================================================================
' Loop through the sheets collection, replacing the names with
' the actual range references
For Each wks In ThisWorkbook.Worksheets

wks.Cells.SpecialCells(xlCellTypeFormulas) _
.Replace szWhat, szReplace, xlPart, , True

Next wks
'
===================================================================


NameMatch:

i = i + 1

Next nm

End Sub

This converts the name of the range into the range behind it. Hence I still
get the entire range in the formula. For example when I run it I get
=Sheet1!$A$1:$G$1 in B1. I am running this in my Personal Workbook do I
changed all your references to Thisworkbook to ActiveWorkbook.

EM

Sub ReplaceNamesWithRefs()
' Loop counter
Dim i As Integer
Dim szWhat As String
Dim szReplace As String
Dim wks As Worksheet
Dim rRng As Range

i = 1

'On Error Resume Next
' Loop through all names in the workbook
Dim nm As Name
For Each nm In ActiveWorkbook.Names
'Store each name object in a variable
szWhat = ActiveWorkbook.Names(i).Name
'In case we find cells that match the defined name's name
For Each rRng In Cells.SpecialCells(xlCellTypeConstants)
If rRng.Value = szWhat Then GoTo NameMatch
Next rRng
'Store each names RefersTo value in a variable that trims the "="
sign
szReplace = Replace(ActiveWorkbook.Names(i).RefersTo, "=", Empty)
'===================================================================
' Loop through the sheets collection, replacing the names with
' the actual range references
For Each wks In ActiveWorkbook.Worksheets
wks.Cells.SpecialCells(xlCellTypeFormulas) _
.Replace szWhat, szReplace, xlPart, , True
Next wks
'====================================================================
NameMatch:
i = i + 1
Next nm
End Sub

"JLXL via OfficeKB.com" wrote:

> In a standard module: Not perfect, so have a backup:
>
> '================================================
> Option Explicit
>
> Sub ReplaceNamesWithRefs()
> ' Loop counter
> Dim i As Integer
> Dim szWhat As String
> Dim szReplace As String
> Dim wks As Worksheet
> Dim rRng As Range
>
> i = 1
>
> On Error Resume Next
> ' Loop through all names in the workbook
> Dim nm As Name
> For Each nm In ThisWorkbook.Names
>
>
> ' Store each name object in a variable
> szWhat = ThisWorkbook.Names(i).Name
>
>
> ' In case we find cells that match the defined name's name
> For Each rRng In Cells.SpecialCells(xlCellTypeConstants)
> If rRng.Value = szWhat Then Goto NameMatch
> Next rRng
>
>
> ' Store each names RefersTo value in a variable that trims the
> "=" sign
> szReplace = Replace(ThisWorkbook.Names(i).RefersTo, "=", Empty)
>
>
> '
> ===================================================================
> ' Loop through the sheets collection, replacing the names with
> ' the actual range references
> For Each wks In ThisWorkbook.Worksheets
>
> wks.Cells.SpecialCells(xlCellTypeFormulas) _
> .Replace szWhat, szReplace, xlPart, , True
>
> Next wks
> '
> ===================================================================
>
>
> NameMatch:
>
> i = i + 1
>
> Next nm
>
> End Sub
>

As you stated, the formulas work correctly.
What else are you trying to achieve what you need the single-cell addresses
for?

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"ExcelMonkey" <[email protected]> wrote in message
news:[email protected]...
> This converts the name of the range into the range behind it. Hence I
> still
> get the entire range in the formula. For example when I run it I get
> =Sheet1!$A$1:$G$1 in B1. I am running this in my Personal Workbook do I
> changed all your references to Thisworkbook to ActiveWorkbook.
>
> EM
>
> Sub ReplaceNamesWithRefs()
> ' Loop counter
> Dim i As Integer
> Dim szWhat As String
> Dim szReplace As String
> Dim wks As Worksheet
> Dim rRng As Range
>
> i = 1
>
> 'On Error Resume Next
> ' Loop through all names in the workbook
> Dim nm As Name
> For Each nm In ActiveWorkbook.Names
> 'Store each name object in a variable
> szWhat = ActiveWorkbook.Names(i).Name
> 'In case we find cells that match the defined name's name
> For Each rRng In Cells.SpecialCells(xlCellTypeConstants)
> If rRng.Value = szWhat Then GoTo NameMatch
> Next rRng
> 'Store each names RefersTo value in a variable that trims the "="
> sign
> szReplace = Replace(ActiveWorkbook.Names(i).RefersTo, "=", Empty)
>
> '===================================================================
> ' Loop through the sheets collection, replacing the names
> with
> ' the actual range references
> For Each wks In ActiveWorkbook.Worksheets
> wks.Cells.SpecialCells(xlCellTypeFormulas) _
> .Replace szWhat, szReplace, xlPart, , True
> Next wks
>
> '====================================================================
> NameMatch:
> i = i + 1
> Next nm
> End Sub
>
> "JLXL via OfficeKB.com" wrote:
>
>> In a standard module: Not perfect, so have a backup:
>>
>> '================================================
>> Option Explicit
>>
>> Sub ReplaceNamesWithRefs()
>> ' Loop counter
>> Dim i As Integer
>> Dim szWhat As String
>> Dim szReplace As String
>> Dim wks As Worksheet
>> Dim rRng As Range
>>
>> i = 1
>>
>> On Error Resume Next
>> ' Loop through all names in the workbook
>> Dim nm As Name
>> For Each nm In ThisWorkbook.Names
>>
>>
>> ' Store each name object in a variable
>> szWhat = ThisWorkbook.Names(i).Name
>>
>>
>> ' In case we find cells that match the defined name's name
>> For Each rRng In Cells.SpecialCells(xlCellTypeConstants)
>> If rRng.Value = szWhat Then Goto NameMatch
>> Next rRng
>>
>>
>> ' Store each names RefersTo value in a variable that trims
>> the
>> "=" sign
>> szReplace = Replace(ThisWorkbook.Names(i).RefersTo, "=", Empty)
>>
>>
>> '
>> ===================================================================
>> ' Loop through the sheets collection, replacing the names
>> with
>> ' the actual range references
>> For Each wks In ThisWorkbook.Worksheets
>>
>> wks.Cells.SpecialCells(xlCellTypeFormulas) _
>> .Replace szWhat, szReplace, xlPart, , True
>>
>> Next wks
>> '
>> ===================================================================
>>
>>
>> NameMatch:
>>
>> i = i + 1
>>
>> Next nm
>>
>> End Sub
>>

It sounds like the longer code does what your shorter code already does.

Is that not the case?

--
Regards,
Tom Ogilvy

"ExcelMonkey" <[email protected]> wrote in message
news:[email protected]...
> This converts the name of the range into the range behind it. Hence I
still
> get the entire range in the formula. For example when I run it I get
> =Sheet1!$A$1:$G$1 in B1. I am running this in my Personal Workbook do I
> changed all your references to Thisworkbook to ActiveWorkbook.
>
> EM
>
> Sub ReplaceNamesWithRefs()
> ' Loop counter
> Dim i As Integer
> Dim szWhat As String
> Dim szReplace As String
> Dim wks As Worksheet
> Dim rRng As Range
>
> i = 1
>
> 'On Error Resume Next
> ' Loop through all names in the workbook
> Dim nm As Name
> For Each nm In ActiveWorkbook.Names
> 'Store each name object in a variable
> szWhat = ActiveWorkbook.Names(i).Name
> 'In case we find cells that match the defined name's name
> For Each rRng In Cells.SpecialCells(xlCellTypeConstants)
> If rRng.Value = szWhat Then GoTo NameMatch
> Next rRng
> 'Store each names RefersTo value in a variable that trims the "="
> sign
> szReplace = Replace(ActiveWorkbook.Names(i).RefersTo, "=", Empty)
>
'===================================================================
> ' Loop through the sheets collection, replacing the names
with
> ' the actual range references
> For Each wks In ActiveWorkbook.Worksheets
> wks.Cells.SpecialCells(xlCellTypeFormulas) _
> .Replace szWhat, szReplace, xlPart, , True
> Next wks
>
'====================================================================
> NameMatch:
> i = i + 1
> Next nm
> End Sub
>
> "JLXL via OfficeKB.com" wrote:
>
> > In a standard module: Not perfect, so have a backup:
> >
> > '================================================
> > Option Explicit
> >
> > Sub ReplaceNamesWithRefs()
> > ' Loop counter
> > Dim i As Integer
> > Dim szWhat As String
> > Dim szReplace As String
> > Dim wks As Worksheet
> > Dim rRng As Range
> >
> > i = 1
> >
> > On Error Resume Next
> > ' Loop through all names in the workbook
> > Dim nm As Name
> > For Each nm In ThisWorkbook.Names
> >
> >
> > ' Store each name object in a variable
> > szWhat = ThisWorkbook.Names(i).Name
> >
> >
> > ' In case we find cells that match the defined name's
name
> > For Each rRng In Cells.SpecialCells(xlCellTypeConstants)
> > If rRng.Value = szWhat Then Goto NameMatch
> > Next rRng
> >
> >
> > ' Store each names RefersTo value in a variable that
trims the
> > "=" sign
> > szReplace = Replace(ThisWorkbook.Names(i).RefersTo, "=", Empty)
> >
> >
> > '
> > ===================================================================
> > ' Loop through the sheets collection, replacing the names
with
> > ' the actual range references
> > For Each wks In ThisWorkbook.Worksheets
> >
> > wks.Cells.SpecialCells(xlCellTypeFormulas) _
> > .Replace szWhat, szReplace, xlPart, , True
> >
> > Next wks
> > '
> > ===================================================================
> >
> >
> > NameMatch:
> >
> > i = i + 1
> >
> > Next nm
> >
> > End Sub
> >