Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Named Cell test
How do I find out if a named cell exists or not from VB (if it doesn't exist
I will the redefine which I know how to do). Regards, Brett |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Named Cell test
Dim myRange as Range
Set myRange = Nothing on error resume next Set myRange = aws.range("TestName") 'Assumes it's a worksheet name 'or 'Set myRange = Range("TestName") 'assumes it's a workbook name on error goto 0 if not myRange is nothing then 'The range exists else 'The range does not exist end if HTH, Barb Reinhardt "Brettjg" wrote: How do I find out if a named cell exists or not from VB (if it doesn't exist I will the redefine which I know how to do). Regards, Brett |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Named Cell test
Thanks Barb. I started fooling around with a test in the sheet to give a
#ref! and then test for that in VB, but I think your solution will be much more elegant! Regards, Brett. "Barb Reinhardt" wrote: Dim myRange as Range Set myRange = Nothing on error resume next Set myRange = aws.range("TestName") 'Assumes it's a worksheet name 'or 'Set myRange = Range("TestName") 'assumes it's a workbook name on error goto 0 if not myRange is nothing then 'The range exists else 'The range does not exist end if HTH, Barb Reinhardt "Brettjg" wrote: How do I find out if a named cell exists or not from VB (if it doesn't exist I will the redefine which I know how to do). Regards, Brett |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Named Cell test
you could try:
Sub test() Dim rngTemp As Range On Error Resume Next Set rngTemp = Range("NameTest") On Error GoTo 0 If rngTemp Is Nothing Then MsgBox "Does not exist" Else: MsgBox rngTemp.Address(, , , True) End If End Sub "Brettjg" wrote: How do I find out if a named cell exists or not from VB (if it doesn't exist I will the redefine which I know how to do). Regards, Brett |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Named Cell test
Thanks JMB. I must be getting better at this - in the interim I came up with
If Range("last.account.test").Value = CVErr(xlErrRef) Then Range("A27").End(xlDown).Select ActiveWorkbook.Names.Add Name:="last.account", RefersToR1C1:=ActiveCell End If AND IT WORKS! Thanks for your help, Brett "JMB" wrote: you could try: Sub test() Dim rngTemp As Range On Error Resume Next Set rngTemp = Range("NameTest") On Error GoTo 0 If rngTemp Is Nothing Then MsgBox "Does not exist" Else: MsgBox rngTemp.Address(, , , True) End If End Sub "Brettjg" wrote: How do I find out if a named cell exists or not from VB (if it doesn't exist I will the redefine which I know how to do). Regards, Brett |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Test if CELL is in RANGE | Excel Worksheet Functions | |||
Calculate mean of test scores from rows of test answers | Excel Discussion (Misc queries) | |||
inserting a named range into new cells based on a named cell | Excel Discussion (Misc queries) | |||
How can i test a cell for bold style in a cell (Excel 2003)? | Excel Worksheet Functions | |||
How to test a cell to see if it is over a threshold? | New Users to Excel |