Run-Time error 1004 Excel 2013

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.

  option_explicit
         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.

  option_explicit_number
         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'.


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.