Replacing Names of NamedRanges Not Working

[programmer123]

I have a worksheet that contains formulas which in turn contains names. Using VBA, I'd like to replace all names that occur in formulas with value of the cell that the name refers to. The code below does not replace names when the value of the cell the name refers to is a text string. For example, my code does not replace the word "YellowCell" in the formula "=CONCATENATE(YellowCell,"Target Formula Containing Name to Replace")" with value of the range it refers to, which if its "*****", should modify the formula to read "=CONCATENATE("****","Target Formula Containing Name to Replace")".

Sorry if I've already confused you... to make it easier, I've included an example of a spreadsheet that exhibits the behavior. Just click on the RunCode button. Remember to enable macros!

What am I missing here? Any suggestions to improving the code?

VBA Code:

Sub RunCode()

    'replace all names in sheet with references
    Dim vA, vY As Variant
    Dim nX As Name
    
    vA = ""
    
    For Each nX In ActiveWorkbook.Names
        vY = vA
        If IsError(Range(nX.RefersTo).Value) = True Then
            vY = 1
        ElseIf IsNumeric(Range(nX.RefersTo).Value) Then
            vY = Range(nX.RefersTo).Value
        ElseIf IsEmpty(nX.RefersTo) = True Then
            vY = 1
        ElseIf Range(nX.RefersTo).Cells.Count > 1 Then
            vY = 1
        Else
'            vY = Range(nX.RefersTo).Value
'            vY = """""" & Range(nX.RefersTo).Value & """"""
            vY = Format(Range(nX.RefersTo).Value)
        End If
        
        Selection.Replace What:=nX.Name, Replacement:=vY, LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
        ReplaceFormat:=False
        
    Next nX

End Sub

 

[Sal Paradise]

Your code isn't attached.

Please copy-paste it using [code][/code] tags.

 

[programmer123]

Btw, I couldn't figure out how to attach the attachment. Happy to send the excel file that illustrates the problem to anyone who requests so.

Thanks

 

[programmer123]

just posted the VBA code... thanks!