Finding named ranges

[Barb Reinhardt]

I have a list of range names that are in my workbook listed on Sheet1 and I
want to determine if these range names are referenced anywhere in the
workbook. This is what I have so far:

Sub FindRange()
Dim LastRow
Dim rangename
Dim CurBook
Dim i
Dim datasheet

datasheet = "Sheet1"
CurBook = Application.ActiveWorkbook.Name
LastRow = Worksheets(datasheet).Cells(Rows.Count, "a").End(xlUp).row

For i = 2 To LastRow
rangename = Workbooks(CurBook).Worksheets(datasheet).Range("a" & i).Value
Debug.Print rangename
'How do I find if this range name is used in the workbook?
'If used put "YES" in C&i
Workbooks(CurBook).Worksheets(datasheet).Range("c" & i).Value = "YES"
'If not used, put "NO" in C&i
Workbooks(CurBook).Worksheets(datasheet).Range("c" & i).Value = "NO"
Next

End Sub

Thanks in advance,
Barb Reinhardt

 

[Tom Ogilvy]

Here is a function to test:

Public Function FindNames(sName As String, bk as Workbook, datasheet)
Dim sh As Worksheet
Dim rng As Range
On Error goto ErrHandler
For Each sh In bk.Worksheets
if lcase(sh.Name) <> lcase(datasheet) then
Set rng = sh.Cells.Find(What:=sName, _
After:=sh.Range("IV65536"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
FindNames = "Yes"
Exit Function
End If
End if
Next
FindNames = "No"
Exit Function
ErrHandler:
FindNames = "Error"
End Function

=======================
then in your code:
Dim rangename as String
Dim bk as Workbook
Dim datasheet as String

set bk = Worksheets(CurBook)
For i = 2 To LastRow
rangename = Workbooks(CurBook).Worksheets(datasheet).Range("a" & i).Value
Debug.Print rangename
Workbooks(CurBook).Worksheets(datasheet) _
.Range("c" & i).Value = FindNames(rangename, bk, datasheet)
Next

 

[Barb Reinhardt]

I'm getting an error on the last line shown here:

Dim LastRow
Dim CurBook
Dim i
Dim rangename As String
Dim bk As Workbook
Dim datasheet As String

datasheet = "Named Ranges"
CurBook = Application.ActiveWorkbook.Name
Debug.Print CurBook
Set bk = Worksheets(CurBook)

Subscript out of range. What's the problem???

Thanks.

 

[Dave Peterson]

bk is a workbook???

Then this:
Set bk = Worksheets(CurBook)
should probably be:
Set bk = Workbooks(CurBook)

 

[Barb Reinhardt]

Thank you, that did it.

bk is a workbook???

Then this:
Set bk = Worksheets(CurBook)
should probably be:
Set bk = Workbooks(CurBook)

 

[Barb Reinhardt]

Dave,

Now I have another related problem. I have a list of Named Ranges and when
I ran this macro, only one "range" was identified. The name was "Month". I
have a lot of entries using the MONTH formula. Is there a quick way to find
if the named range MONTH is used? I'm guessing this macro displays if the
text in the range is used in a formula.

Thanks,
Barb Reinhardt

 

[Tom Ogilvy]

Dave gave you the correction to the typo - sorry about that -

but just be aware that this only finds name usage in formulas in a worksheet
- there could be many other places it might be used - such as in chart
references, formulas in conditional formatting and data validation - just to
name a few.

 

[Tom Ogilvy]

download the name manager from JKP's site:

http://www.jkp-ads.com/Download.htm