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
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,048
Messages
5,569,879
Members
412,298
Latest member
dietitiann
Top