Run-Time error 1004 Excel 2013

by

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

More Related Contents:

  1. Save excel file multiple times with file names in another workbook.
  2. Read/Parse text file line by line in VBA
  3. How do I find the last column with data?
  4. Excel VBA: Answer gets “stuck”
  5. How to insert a picture into Excel at a specified cell position with VBA
  6. Save multiple sheets to .pdf
  7. Having multiple Excel instances launched, how can I get the Application Object for all of them?
  8. Excel VBA autofilter all but three
  9. How can I create a calendar input in VBA Excel?
  10. Cells.Find() Raises “Runtime Error 91: Object Variable or With Block Not Set”
  11. Getting the actual usedrange
  12. Delete a row in Excel VBA
  13. Concatenate cell data into another data if values matches
  14. Iterating through populated rows
  15. How can I run VBA code each time a cell gets its value changed by a formula?
  16. How to convert 32 bit VBA code into 64 bit VBA code
  17. Using VBA to detect which decimal sign the computer is using
  18. Loop through each row of a range in Excel
  19. Outputting Excel rows to a series of text files
  20. How to create a range from two ranges in VBA?
  21. XML parse VBA excel (function trip, & MSXML2.DOMDocument)
  22. If string contains word from list, return value adjacent to list value
  23. every possible combination of the contents of 2 columns in excel
  24. excel vba freeze pane without select
  25. How to delete rows in Excel based on criteria using VBA?
  26. Converting a string representation of a constant into a constant?
  27. Create folder path if does not exist (saving from VBA)
  28. Access files with long paths (over 260)
  29. Concatenate multiple ranges using vba
  30. How to fix a VBA “type mismatch” error after switching to 64-bit Excel

Leave a Comment