View Single Post
  #6   Report Post  
Posted to microsoft.public.excel.programming
Barb Reinhardt Barb Reinhardt is offline
external usenet poster
 
Posts: 3,355
Default Finding named ranges

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

"Dave Peterson" wrote:

bk is a workbook???

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

Barb Reinhardt wrote:

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



--

Dave Peterson