VBA Excel - Avoid not exsisting rangenames when copy/paste data from one rangename to another rangename

Question

From cells in a sheet (Sheets("Omrnavne")) with several Range names written below each other the code has to find the first value (range name) written, find and select the actual range with this name on Sheets("Specifikationer"), copy data, go to the next value on Sheets("Omrnavne"), find the actual range, paste values and start over.

Problem: if there is written a range name on Sheets("Omrnavne") that don't exist as range name how do one skip this?

With the following code: If Range(whatToFind) Is Nothing Then
I get the error:

Run-time error '1404': Method 'Range' of object'_Global' failed

when searching for a rangename that dosen't exist.

I've tried for hours but with no success. Please help - thanks.

The code is:

Sub Test1()
    Dim whatToFind As String
    Sheets("Omrnavne").Select

    ' Select first line of data*.
    Range("a1").Select

    ' Set Do loop to stop when an empty cell is reached.
    Do Until IsEmpty(ActiveCell)
        whatToFind = ActiveCell.Value

       'Find rangename, select and copy
        Sheets("Specifikationer").Select
        With Sheets("Specifikationer")
            On Error Resume Next
            On Error GoTo 0

            If Range(whatToFind) Is Nothing Then ' <~~~~ ERROR HERE    
                Sheets("Omrnavne").Select
                ActiveCell.Offset(2, 0).Select
                whatToFind = ActiveCell.Value           
            Else       
                Range(whatToFind).Select
                Selection.Copy
            End If                
        End With

        ' Find and select rangename to paste to
        Sheets("Omrnavne").Select
        With Sheets("Omrnavne").Select
             ActiveCell.Offset(1, 0).Select
             whatToFind = ActiveCell.Value        
             Sheets("Specifikationer").Select
             Range(whatToFind).Select
             Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                 :=False, Transpose:=False
             Application.CutCopyMode = False
        End With

        ' Step down 1 row from present location.
        Sheets("Omrnavne").Select
        ActiveCell.Offset(1, 0).Select         
    Loop

End Sub

score 0 · Accepted Answer · answered Nov 05 '14 at 09:49

As you have seen already, the ActiveSheet.Range property returns not Nothing, if the range does not exists. Instead it will produce an error. You can DIM an object variable as Range. This variable is Nothing until it is setted. Then you can try setting this variable to the named range within an error handling block. If the variable is further Nothing after this, then the named range not exists.

Example:

 Dim oRange As Range
 On Error Resume Next
 Set oRange = Range("test")
 On Error GoTo 0
 If oRange Is Nothing Then
  MsgBox "No range named ""test"" present."
 Else
  MsgBox oRange.Address
 End If

Greetings

Axel

VBA Excel - Avoid not exsisting rangenames when copy/paste data from one rangename to another rangename

1 Answers1