If name range is not found then exit sub. Please help

[pedie]

I have the code below which works ok if name range if found but if name range is not found then it is giving error..

Please help.
Thanks
Pedie

Sub test21()
Dim r As String
Sheets("Sheet1").Select
   If Range(r) Is Nothing Then
     MsgBox "Name missing..."
     Exit Sub
     Else
     ThisWorkbook.Sheets("Data").Range("C2:C14").Copy
          Range(r).Select
           Do Until ActiveCell.Value = ""
            ActiveCell.Offset(1, 0).Select
             Loop
              Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
     xlNone, SkipBlanks:=False, Transpose:=True
             
            Application.CutCopyMode = False
          MsgBox "Ok"
          Application.ScreenUpdating = True
    End If
End Sub

 

[VoG]

You haven't assigned a value to r.

 

[pedie]

Sorry

r = ThisWorkbook.Sheets("Data").Range("C4").Value

 

[VoG]

Try

Sub test21()
Dim r As String
r = ThisWorkbook.Sheets("Data").Range("C4").Value
Sheets("Sheet1").Select
   If Not NameExists(r) Then
     MsgBox "Name missing..."
     Exit Sub
     Else
     ThisWorkbook.Sheets("Data").Range("C2:C14").Copy
          Range(r).Select
           Do Until ActiveCell.Value = ""
            ActiveCell.Offset(1, 0).Select
             Loop
              Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
     xlNone, SkipBlanks:=False, Transpose:=True
             
            Application.CutCopyMode = False
          MsgBox "Ok"
          Application.ScreenUpdating = True
    End If
End Sub

Function NameExists(aName As String) As Boolean
Dim S As String
On Error GoTo EndFunction
S = ThisWorkbook.Names(aName).RefersTo
NameExists = True
Exit Function
EndFunction:
NameExists = False
End Function

 

[pedie]

Do I put both the codes on standard module?

 

[VoG]

Yes.

 

[pedie]

Lemmi go ahead and try again!
Thanks Peter!

 

[pedie]

if I am using code to open another workbook(book2) and then look for name in that book2 will the code
change to ActiveWorkbook.Names(aName).RefersTo from Thisworkbook?

Function NameExists(aName As String) As Boolean
Dim S As String
On Error GoTo EndFunction
S = ActiveWorkbook.Names(aName).RefersTo
NameExists = True
Exit Function
EndFunction:
NameExists = False
End Function

 

[VoG]

It will check for the existence of the named range in the active workbook - the one that you just opened.

 

[pedie]

Peter, thank alot for helping & clearing things up...

Have a great day ahead!