How do I use Split on a named range?

Question

I'm trying to look through formulas in a range of cells and match up named ranges in the worksheet to any named ranges in the formula and replace the named ranges with their reference cells.

Dim rng As Range
Dim workrng As Range
Dim xname As Name
Dim arr() As Variant
Dim arr_string as String

Set workrng = Application.Selection
Set workrng = Application.InputBox("txt", "header txt", workrng.Address, Type:=8
Set workrng = workrng.SpecialCells(xlCellTypeFormulas)

For Each rng In workrng
    For Each xname in ThisWorkbook.names
        arr = Split(xname.Name, "!")
        arr_string = arr(1)
        Debug.Print arr_string
        If InStr(rng.Formula, xname.Name) > 0 Then
            rng.Formula = VBA.Replace(rng.Formula, xname.Name, VBA.Replace(xname.RefersTo, "=", ""))
        End If
    Next
Next

When I debug print "xname.Name" I get results that include the sheet name. For example: Specifications!_MyNamedRange. However, the formula in the cell only says: _MyNamedRange. That's why I'm trying to split it. However, when I debug my array, there is nothing in it.

Hard without data. But the sheet name usually goes with the .RefersTo property. — Ron Rosenfeld
I think we need a bit more explanation of what you actually want to happen here. Because if you're just replacing the named range's name within the formula, the code you use should be working just fine. The fact that you try to pull the absolute cell reference (eg $A$1:$B$2) out of the named range confuses me regarding what you actually want to accomplish — Marcucciboy2
@RonRosenfeld correct. But for some reason when I debug xname.Name it returns the named range with the sheet name (ie, Specifications!_MyRangedName) rather than just the named range itself. — AP1
@Marcucciboy2 The code I was using does work on other workbooks. For some reason it is behaving differently here. I'm pulling the absolute cell reference because I want to replace the named ranges in formulas for specific cells because I'm deleting out those named ranges from the workbook and before I do so, I want to fix the formulas so I don't end up with #NAME? errors. — AP1
Since the problem exists on one specific workbook, the easiest method to figure this out would be to examine the workbook itself. If you can upload a copy of one that demonstrates the problem (and edit your question to include a link), someone should be able to help you. I will be away for a few days and, if you haven't received an answer, will check back Wed or Thu — Ron Rosenfeld

Ron Rosenfeld Ron Rosenfeld · Accepted Answer · 2018-09-23T00:19:01

Hard to know exactly what to do without a worksheet. But a couple of changes you need to make:

Dim arr() As Variant --> Dim arr As Variant

arr = Split(xname.Name, "!") --> arr = Split(xname.RefersTo, "!")

How do I use Split on a named range?

1 Answers