Run-time Error 1004 on my VBA Excel code - transfer data from one worksheet to another

Question

VBA Code to transfer value of one cell to another worksheet

Sub Button4_Click()

Dim Description As String

Worksheets("Job Order Format").Select

Description = Range("C20")

Worksheets("Job Order Record").Select

Worksheets("Job Order Record").Range("E5").Select

If Worksheets("Job Order Record").Range("E5").Offset(1, 0) <> "" Then

Worksheets("Job Order Record").Range("E5").End(x1Down).Select

End If

ActiveCell.Offset(1, 0).Select

ActiveCell.Value = Description

Worksheets("Job Order Format").Select

Worksheets("Job Order Format").Range("C20").Select

End Sub

The code works for the 1st attempt with no error but for the 2nd attempt I get an error of Run-time Error 1004.

Answer 1

First of all you have to read this article carefully.

The reason because your error is raised only on the 2nd run is because:

1. On the first run, you have an empty range Worksheets("Job Order Record").Range("E5").Offset(1, 0);
2. That range is filled with ActiveCell.Value = Description line;
3. On the second run, you match the If condition and try to perform the line Worksheets("Job Order Record").Range("E5").End(x1Down).Select;
4. You get an error.

So what do you need to do? The solution is very easy:

1. In your editor, go to Tools → Options → tick the "Require Variable Declaration":

2. Then go to Debug → Compile VBAProject:

3. You see the reason of error at once - it is misprint of direction .End(*x1Down*) variable (you have number 1 instead of l letter):

As far as you have the "Require Variable Declaration" switched off -compiler doesn't check the code before run, but when code reaches the line with error - it can't understand what to do and throws an exception.

The other thing is that if you do read the article - you would likely replace 12 lines of your code with only 6, a bit faster code, something like this:

Sub Button4_Click()

Dim Description As String
Dim OrderFormatSht As Worksheet, OrderRecordSht As Worksheet

Set OrderFormatSht = ThisWorkbook.Sheets("Job Order Format")
Set OrderRecordSht = ThisWorkbook.Sheets("Job Order Record")

Description = OrderFormatSht.Range("C20")

If Not Description = "" Then OrderRecordSht.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0) = Description

End Sub