So there's a known bug in excel where applying named ranges via ribbon doesn't work properly (for 2010 and 2013 and possibly 2016). There was a question about it on superuser - Apply names in excel bugged?. I took a shot at it.
This code will find all of the named ranges in the book and then search a specified range for any cells that have formulas containing a reference tied to the named range's address and replace that with the named range.
This does that, but only works for absolute references when searching as I couldn't figure out a simple way to get the named range's relative reference.
I guess I'm wondering if there's a better way to do this (maybe with a dictionary so I don't need two loops?) and also capture the relative references. Or if there are any properties I didn't use that will do what I want.
Option Explicit Sub FixNames() 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