Trying to Replace Named Ranges with Cell References

[The_Accountant]

I have a workbook with 25-30 worksheets and every single cell that would have a cell reference uses a named range instead. I am trying to replace them all with cell references using the following macro (found on google):

Sub AbsoleteNamesWithRelativeRefs()
Dim Rng As Range
Dim WorkRng As Range
Dim xName As Name
On Error Resume Next
xTitleId = "Input_Range"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Set WorkRng = WorkRng.SpecialCells(xlCellTypeFormulas)
For Each Rng In WorkRng
For Each xName In ThisWorkbook.Names
If InStr(Rng.Formula, xName.Name) > 0 Then
Rng.Formula = VBA.Replace(Rng.Formula, xName.Name, VBA.Replace(VBA.Replace(xName.RefersTo, "=", ""), "$", ""))
End If
Next
Next
End Sub

The named ranges are based on dates - some examples are:
=_12stdjan1
=_12stdjan14
=_12stdjan15

For any ranges that have a two digit day (e.g. 2nd and 3rd example above), the macro is only recognizing the name through the tens place and leaving the final digit at the end of the resulting cell reference. So it pulls the cell reference for the Jan 1 date and makes the resulting cell reference have an extra digit.

For example, if the name "_12stdjan14" refers to cell $A$15 and the name "_12stdjan1" refers to cell $B$15, running the macro for "_12stdjan14" would result in a cell reference of $B$154.

Is there any way to fix this issue? I am unfortunately very inexperienced with VBA if that isn't already apparent.

 

[The_Accountant]

Also, I think it makes much more sense that if the named range was in the sheet of focus, that the formulas in that (same) sheet which reference it should not have SheetName! in front of the range address.

You can simply insert the following IF statement that's center line of this loop to "fix" that.

        For i = UBound(arrayOfNamedRangesInCurrentCellFormula) To 1 Step -1
            refersTo_replacement = Replace(Replace(ThisWorkbook.Names.Item(arrayOfNamedRangesInCurrentCellFormula(i)).RefersTo, "=", ""), "$", "")
            If SubString(refersTo_replacement, 1, InStr(refersTo_replacement, "!") - 1) = sht.Name Then refersTo_replacement = SubString(refersTo_replacement, InStr(refersTo_replacement, "!") + 1, Len(refersTo_replacement))
            tempFormula = Replace_NamedRanges(tempFormula, "[&%^*/+-=>< ,:)(]" & arrayOfNamedRangesInCurrentCellFormula(i) & "[&%^*/+-=>< ,:)(]", arrayOfNamedRangesInCurrentCellFormula(i), refersTo_replacement)
        Next i

And named ranges are technically absolute cell references $A$1, so it may also make sense (if you want to keep the "functionality" of the sheet, to omit the Range.Replace() $ with "")

        For i = UBound(arrayOfNamedRangesInCurrentCellFormula) To 1 Step -1
            refersTo_replacement = Replace(ThisWorkbook.Names.Item(arrayOfNamedRangesInCurrentCellFormula(i)).RefersTo, "=", "")
            If SubString(refersTo_replacement, 1, InStr(refersTo_replacement, "!") - 1) = sht.Name Then refersTo_replacement = SubString(refersTo_replacement, InStr(refersTo_replacement, "!") + 1, Len(refersTo_replacement))
            tempFormula = Replace_NamedRanges(tempFormula, "[&%^*/+-=>< ,:)(]" & arrayOfNamedRangesInCurrentCellFormula(i) & "[&%^*/+-=>< ,:)(]", arrayOfNamedRangesInCurrentCellFormula(i), refersTo_replacement)
        Next i

But another regular expression substitution would need to be done to make just the cell references that were previously named ranges (as we can have a mixture) as absolute. Maybe I can get to this later. Just let me know.

Wow - thank you so much! This worked amazingly well. You are really doing the Lord's work - that saved me so many headaches. The workbook I'm dealing with is a nightmare to follow as every single cell reference is a named range. Many of the names are duplicated with a different scope which made it even worse. Sorry I couldn't post the workbook as an example - confidentiality and all that.

Anyway thank you again!

 

[The_Accountant]

Just as a follow up, if the ONLY Defined Names that start with an underline character and end with a digit refer to these particular Names that you are having trouble with in the code you posted, then you can use this code to insert the 0 before the last digit when there is only a single digit at the end...

Sub MakeSingleDigitDayNumberToTwoDigits()
  Dim N As Name
  For Each N In Names
    If N.Name Like "_*[!0-9]#" Then N.Name = Application.Replace(N.Name, Len(N.Name), 0, "0")
  Next
End Sub

Note: This code will process look at every Defined Name in the workbook, not just the Names on the active sheet.

Thanks for the reply! I tried your solution first for simplicity but this particular workbook just has too many duplicated names with different scopes. I am going to keep this one in my back pocket as it is likely the easiest solution and would work 99% of the time this issue comes up.

 

[cmowla]

Wow - thank you so much! This worked amazingly well. You are really doing the Lord's work - that saved me so many headaches. The workbook I'm dealing with is a nightmare to follow as every single cell reference is a named range. Many of the names are duplicated with a different scope which made it even worse. Sorry I couldn't post the workbook as an example - confidentiality and all that.

Anyway thank you again!

You're welcome. (This was really fun!) And of course if you don't want the named ranges, you can run that little sub that I first mentioned after you run the big one!