Sub FixNames()
'Alternative to "Apply Names" which is buggy
'https://codereview.stackexchange.com/questions/112885/using-vba-to-apply-names-to-named-range-references
Dim ClctNames As Variant
Set ClctNames = ActiveWorkbook.Names
Dim rngName As String
Dim rngNameLoc As String
Dim strFrmla As String
Dim c As Range
Dim n As Integer
'Define as needed
Dim srchRng As Range
Set srchRng = ActiveSheet.UsedRange
'For each name (n) in the collection
For n = 1 To ClctNames.Count
'I'm storing the Named Range's name and address as strings to use below
rngName = ClctNames(n).Name
rngNameLoc = ClctNames(n).RefersToRange.Address
'--Should I break this out into a function? If so, at what point?
For Each c In srchRng
'We only want to test cells with formulas
If c.HasFormula = True Then
'We have to check if the cell contains the current named range's address
If InStr(1, c.Formula, rngNameLoc, vbTextCompare) <> 0 Then
'Since these are perfect matches, no need to look for length or location, just replace
strFrmla = Replace(c.Formula, rngNameLoc, rngName)
c.Formula = strFrmla
End If
End If
Next
Next
'No error handling should be needed
End Sub