Finding named ranges
I'm getting an error on the last line shown he
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.
"Tom Ogilvy" wrote:
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
--
Regards,
Tom Ogilvy
"Barb Reinhardt" wrote:
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
|