Find/Replace Named Cells

[ccs0148]

I am working with a workbook that has alot of named cells. The named cells are hindering the functionality of another workbook that is linking to it. I want to write a macro that will automatically find and replace all of the named cells in the formulas with the actual cell location. I.E. I am looking to change a formula from =(Invst_Amt*.12) to =(C12*.12) where Invst_Amount is located at C12. I have simplifed the math to make it easier to see. There are about 100 named ranges in the workbook so I am trying to keep from using the Replace function and manually keying in all of the names.

I would appreciate anyones help.

 

[AlphaFrog]

There is a free Excel add-in called Name Manager. It has a feature to replace a Name in all the formulas that use it with its Refers To reference. It also has several other useful features if you use a lot of Names.

 

[ccs0148]

I found this online. It works.

Option Explicit

Public Sub ReplaceNamesWithRefs()
Dim rng As Excel.Range
Dim wks As Excel.Worksheet
Dim nme As Name
Dim nCt As Integer
Dim szWkbName As String
Dim szSwitchString As String

nCt = 1

With Application
.EnableEvents = False

On Error Resume Next
'Loop through all names in the workbook
For Each nme In ThisWorkbook.Names

'Store each name object in a variable
szWkbName = ThisWorkbook.Names(nCt).Name
.StatusBar = "Checking Defined Name " & CStr(szWkbName)

'Store each names RefersTo value in a variable that trims the (=)&($)
szSwitchString = Replace$(ThisWorkbook.Names(nCt).RefersTo, "=", "")
szSwitchString = Replace$(szSwitchString, "$", "")

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

With wks.Cells.SpecialCells(xlCellTypeFormulas)
.Replace$ szWkbName, szSwitchString, xlPart, , True
.Replace$ wks.Name & "!", "", xlPart, , True
End With

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

nCt = nCt + 1

Next nme

'This code portion was created by Jim Rech
For Each wks In ThisWorkbook.Worksheets
With wks
.TransitionFormEntry = True
For Each rng In .Cells.SpecialCells(xlFormulas)
rng.Formula = rng.Formula
Next rng
.TransitionFormEntry = False
End With
Next wks

.EnableEvents = True
.StatusBar = False
End With
End Sub