Solved: Repairing Defined Names

[Cyberdude]

In a workbook, I have a LOT of defined names that need repairing. The defining formula for each name begins with
=#REF (like =#REF!$A$1). In other words, the name of the sheet in each formula is shown as #REF.

I want to change each #REF to be the name of a sheet in that workbook. There are so many to be repaired that I really don?t want to do it by hand one at a time if I can avoid it. I?m hoping one of you kind folks have a ?Find and Replace? technique I can use. Any ideas?

[Bob Phillips]

How do you know which sheet name?

[malik641]

If you know which sheet it is by the name of the Defined Name, then you could do something like:

Public Sub RepairNames()
Dim nName As Name
Dim strRefersTo As String
Dim strSheetName As String

For Each nName In ThisWorkbook.Names
Debug.Print "Before:", nName.RefersTo
strRefersTo = Split(nName.RefersTo, "!")(0)

Select Case nName.Name
Case Is = "ThisName"
strSheetName = "ThisSheet"
Case Is = "ThatName"
strSheetName = "ThatSheet"
End Select

If strRefersTo = "=#REF" And SheetExists(strSheetName) Then
nName.RefersTo = Replace(nName.RefersTo, "#REF!", strSheetName)
End If

Debug.Print "After:", nName.RefersTo
Next
End Sub
Public Function SheetExists(ByVal shName As String) As Boolean
'Determines if a worksheet exists in the workbook
Dim ws As Object
On Error Resume Next
Set ws = ActiveWorkbook.Sheets(shName)
If Err = 0 Then SheetExists = True Else SheetExists = False
End Function
EDIT: Deleted an unnecessary line (in my post, not the code)

HTH :thumb

[Aussiebear]

Just a question here, If the #ref's caused by something which will occur more than once then would you not be better off fixing the cause rather than trying to fix the symptoms?

[malik641]

Just a question here, If the #ref's caused by something which will occur more than once then would you not be better off fixing the cause rather than trying to fix the symptoms? I agree with this. Hopefully Cyberdude will fix both the symptoms and the cause (the #REF!'s and to prevent it from happening in the future).

[malik641]

My apologies, there is something wrong with the code I posted above...please replace the Sub with this:
Public Sub RepairNames()
Dim nName As Name
Dim strRefersTo As String
Dim strSheetName As String

For Each nName In ThisWorkbook.Names
Debug.Print "Before:", nName.RefersTo
strRefersTo = Split(nName.RefersTo, "!")(0)

Select Case nName.Name
Case Is = "ThisName"
strSheetName = "ThisSheet"
Case Is = "ThatName"
strSheetName = "ThatSheet"
End Select

If strRefersTo = "=#REF" And SheetExists(strSheetName) Then
nName.RefersTo = Replace(nName.RefersTo, "#REF", strSheetName)
End If

Debug.Print "After:", nName.RefersTo
Next
End Sub

[Cyberdude]

Thank you all for responding. It was a big help.

I left the impression that the #REF's were the result of corruption or such. That isn't what happened. I did it (without thinking it out) intentionally. I was redesigning a rather complex sheet using a test version of the workbook. When I was happy with the results, I copied the revised sheet to the production workbook. When I did that, then a lot of the defined names were defined a second time. When I looked at the list of defined names, a LOT of them had the name of the sheet on the right side of each name in the list. This indicates that there are two versions of the definition present: one was the original and the the other was override versions for the new sheet I copied into the workbook. Since I wanted only one version to be present, I deleted all the override versions. When I did that I was left with all the original versions having #REF substituted for the sheet name. I have encountered this effect for many years now, and usually there are just a few to contend with, but this time there were a lot. I managed to write a very effective macro to help me, so thanks again for your time.