Named Cell test

[Guest]

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

 

[Guest]

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

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

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