replace naming with actual cell referenceClosed

Question

Hello,
I have a workbook with lot of names in it.
I want to replace those names with actual cell reference.
How can I do that?
Is there is a way to do that without VBA?

sharpman · Answer

example of data and what you are trying to do?

macg · Answer

I think that question was self-explanatory. Unfortunately, the best I could come up with is what I have below. The limitation is that it won't convert names in other workbooks (e.g. 'AnotherWorkbook.xls!foo') to cell references, but it's still better than other solutions I've seen, including the TransitionFormEntry "trick", which only converts names from the same worksheet to cell references. The example below only converts the formula in the ActiveCell, but you can add code to loop thru all cells with formulas in the workbook.

    Dim myRegExp As RegExp
    Dim newEqn As String
    Dim nm As Name

    Set myRegExp = New RegExp
    With myRegExp
        .Global = True
        .IgnoreCase = False
    End With

    newEqn = ActiveCell.Formula
    For Each nm In ActiveWorkbook.Names
        With nm
            If InStr(1, .RefersTo, "#REF!") = 0 Then
                myRegExp.Pattern = "([\=\+\-\*/\^\,\(\)<>\[\]])(" & .Name & ")([\+\-\*/\^\,\(\)<>\[\]]?)"
                newEqn = myRegExp.Replace(newEqn, "$1" & Replace(Right(.RefersTo, Len(.RefersTo) - 1), "$", "$$") & "$3")
            End If
        End With
    Next nm

Replace naming with actual cell reference

2 responses

Similar discussions

Subscribe To Our Newsletter!