programmer123
New Member
- Joined
- May 31, 2005
- Messages
- 7
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:
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:
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
Last edited: