Run-Time error 1004 Excel 2013

问题:

 Private Sub CommandButton1_Click()
 Dim lRow As Long
 Dim ws As Worksheet
 Set ws = Worksheets("Sheet2")
 lRow = ws.Cells(Rows.Count, 1).End(x1Up).Offset(1, 0).Row
 With ws
.Cells(lRow, 1).Value = UserForm1.TextBox1.Value
.Cells(lRow, 2).Value = UserForm1.TextBox2.Value
 End With
 End Sub

I am using this macro to add some entries into cells. I just want to add the 2 values into textboxes and the pressing the button to move the entries into Sheet2. Second time, the 2 entries will be moved below the first entry and so on...

Every time I am pressing the button I am getting the message "Run-Time error 1004".

回答1:

Your problem has been expertly identified by mongoose36 but here is how to locate and address issues like this in the future.

Compile error: Variable not defined

Setting Require Variable Declaration within the VBE's Tools ► Options ► Editor property page will put the Option Explicit statement at the top of each newly created code sheet. This will avoid silly coding mistakes like misspellings as well as influencing you to use the correct variable type in the variable declaration. Variables created on-the-fly without declaration are all of the variant/object type.

  
         ^{Simple variable/constant misspelling compiler error (X1UP should be XLUP)}

When you receive an error during run-time, go to the sub in the VBE and tap F8. The compiler will immediately issue a Compile error message and highlight the offending portion of the statement.

While this will not resolve all compile errors, it makes the simple mistakes quick and easy to identify and correct.

Addendum - Compile error: Syntax error

There is a special case involving misspelled variables that should be addressed. Declaration, assignment or use of a variable with a name that starts with a digit is illegal syntax. The Syntax error supersedes the Variable not defined error; there are two errors but the primary reported error is Syntax error. In this case the entire codeline is highlighted.

  
         ^{Variable misspelling compiler error with digit as first character(1ROW should be LROW)}

You are writing good code with explicit parent worksheet references and not relying upon the implicit ActiveSheet property. Go one step further with the Require variable declaration option. Using Option Explicit is widely considered 'best practice'.

回答2:

Change ws.Cells(Rows.Count, 1).End(x1Up).Offset(1, 0).Row

To ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

The bolded character in End(xlUp) is an l as in lion not a 1 (one).

Also within the userform you can use the following Me.TextBox1.Value. Naming the userform will also work, but the other is quicker to write.