Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Finding named ranges
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,355
Default Finding named ranges
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


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default Finding named ranges
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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,355
Default 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


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default Finding named ranges
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
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,355
Default Finding named ranges
Thank you, that did it.

"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

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
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

Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default Finding named ranges
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.


--
Regards,
Tom Ogilvy



"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


Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default Finding named ranges
download the name manager from JKP's site:

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

--
Regards,
Tom Ogilvy



"Barb Reinhardt" wrote:

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

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
FINDING A NAMED TAB twinpat Excel Discussion (Misc queries) 4 May 19th 09 07:21 PM
Like 123, allow named ranges, and print named ranges WP Excel Discussion (Misc queries) 1 April 8th 05 06:07 PM
named ranges - changing ranges with month selected gr8guy Excel Programming 2 May 28th 04 04:50 AM
Finding all Similarly Named Ranges? Grant Reid Excel Programming 4 May 5th 04 02:24 PM
Named ranges Ron de Bruin Excel Programming 1 April 20th 04 03:56 PM


All times are GMT +1. The time now is 02:50 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement