Checking for named ranges in a workbook

loopoo

New Member
Joined
Nov 10, 2005
Messages
43
Hello!

Can anyone help me with the following problem:

I have some ranges in my workbook, and I want to check if the content of a cell is the name of a range.

For example:

I have : range1
range2
range3
range4
range5

I want to see if ActiveCell.Offset(0,-2).Value is one of the named ranges in the workbook.

How can I make that check???


Thanks in advance,
Chris
 

SiddersAtCHEP

Board Regular
Joined
Nov 2, 2005
Messages
104
Using the code below, a message will let you know if the contents of the activecell matches a range name

Sub RangeCheck()
RangeFound = False
RangeNameFound = ""
RangeCellsFound = ""
For Each rn In ActiveWorkbook.Names
If rn.Name = ActiveCell.Value Then
RangeNameFound = rn.Name
RangeCellsFound = ActiveWorkbook.Names(rn.Name).RefersTo
RangeFound = True
End If
Next

If RangeFound Then
MyMessage = MsgBox(RangeNameFound & " is the name of a range: " & RangeCellsFound, vbOKOnly, "Range Name Found")
Else
MyMessage = MsgBox(ActiveCell.Value & " is not the name of a range.", vbOKOnly, "Range Name Not Found")
End If

End Sub
 

Forum statistics

Threads
1,082,575
Messages
5,366,415
Members
400,888
Latest member
Cdim7

Some videos you may like

This Week's Hot Topics

Top