Run-time error 1004 - Method 'Range' of object'_Global' failed

Question

I'd like to get the data of A and B columns inside .xlsx file, and paste them in the active Workbook, in both BS and BT columns, starting at row 6.

This is the code I've been using in other parts of the macro:

Workbooks.Open ThisWorkbook.Path & "\..\macro\options.xlsx"

Workbooks("options.xlsx").Activate
Set c = .Find("licensePlate", LookIn:=xlValues)
Range(c.Offset(1, 0), Range(c.Address).End(xlDown)).Copy
ThisWorkbook.Activate
Sheets("example").Activate
Range("BS6").PasteSpecial Paste:=xlPasteValues

Workbooks("options.xlsx").Activate
Set c = .Find("description", LookIn:=xlValues)
Range(c.Offset(1, 0), Range(c.Address).End(xlDown)).Copy
ThisWorkbook.Activate
Sheets("example").Activate
Range("BT6").PasteSpecial Paste:=xlPasteValues

Workbooks("options.xlsx").Close

ThisWorkbook.Activate

It worked in all of the macro content except on this portion of code. It fails at line 5, which is:

(Range(c.Offset(1, 0), Range(c.Address).End(xlDown)).Copy)

these type of question has been asked before. Please search the site or/and google — , Jan 21 '15 at 14:48
Already searched it but cannot find an answer exactly valid for me, and I am not a vba expert. — smt, Jan 21 '15 at 14:49
Make sure `c` actually finds something, it might be that `licensePlate` is not found where you look it for. Set a watcher into `c` and run the code in debug to see what it is after the statement `Set c = ...`. — Matteo NNZ, Jan 21 '15 at 14:56
Open "Locals Window", so you can see if your variables are not empty (https://msdn.microsoft.com/en-us/library/office/gg264148(v=office.15).aspx). — Felipe, Jan 21 '15 at 14:59
I did it already before the creation of this thread and exactly the column named "licensePlate" is not found, but it exists in the file. The macro does not select the column. It's strange...the headername is correct =/ — smt, Jan 21 '15 at 14:59
Did you read my answer? I don't think your `.Find` is referring to the correct Range. You need to DEFINE the area you want to search in. — Chrismas007, Jan 21 '15 at 15:06

score 1 · Accepted Answer · edited May 23 '17 at 11:50

You .Find doesn't seem to be referring to a Range because you are not using a With Range. Therefore, c is being set to Nothing and when you try to Offset a Nothing Range you will get the error.

You need to use an error check like

If c is Nothing Then
    Msgbox "licensePlate Not Found"
Else
    'Run Code
End If

If you want to search a whole sheet you could use something like:

Set c = Workbooks("options.xlsx").Sheets("name of sheet").Cells.Find("licensePlate", LookIn:=xlValues)
If c is Nothing Then
    Msgbox "licensePlate Not Found"
Else
    'Run Code
End If

Also, I would highly recommend you avoid Activate. Instead, you should always define the object that you are using a method on.

Edit: You aren't defining your Sheet for the Range either:

Something like this should work:

Dim ws1 as Worksheet, Dim c As Range
Set ws1 = Workbooks("options.xlsx").Sheets("name of sheet")
Set c = ws1.Cells.Find("licensePlate", LookIn:=xlValues)
If c is Nothing Then
    Msgbox "licensePlate Not Found"
Else
    ws1.Range(c.Offset(1, 0), c.End(xlDown)).Copy
    ThisWorkbook.Sheets("example").Range("BS6").PasteSpecial Paste:=xlPasteValues
End If

Run-time error 1004 - Method 'Range' of object'_Global' failed

1 Answers1