{"id":2907,"date":"2018-08-11T09:47:43","date_gmt":"2018-08-11T09:47:43","guid":{"rendered":"http:\/\/officetuts.net\/excel\/?p=2907"},"modified":"2024-03-29T15:48:43","modified_gmt":"2024-03-29T15:48:43","slug":"vba-runtime-error-1004-application-defined-or-object-defined-error","status":"publish","type":"post","link":"https:\/\/officetuts.net\/excel\/vba\/vba-runtime-error-1004-application-defined-or-object-defined-error\/","title":{"rendered":"VBA Runtime Error 1004 \u201cApplication-defined or Object-defined error\u201d"},"content":{"rendered":"\n<p>The VBA Runtime Error 1004 can be caused by many things. In this article, I\u2019ll show a few different situations and how to deal with them.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">VBA code refers to a range that doesn\u2019t exist<\/h3>\n\n\n\n<p>This code usually happens when VBA code refers to a cell or range that doesn\u2019t exist. For example, this code is correct.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Sub myFunc()\n    Set Rng = Worksheets(\"Sheet1\").Range(\"Z3\")\n    Set Rng2 = Worksheets(\"Sheet1\").Range(\"ZZ3\")\nEnd Sub<\/pre>\n\n\n\n<p>But when you try to access the following ranges, the program will return an error in both cases.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Sub myFunc()\n    'Error 1004 \"Application-defined or Object-defined error\"\n    Set Rng = Worksheets(\"Sheet1\").Range(\"ZZZ3\")\n    Set Rng2 = Worksheets(\"Sheet1\").Range(\"myRange\")\nEnd Sub<\/pre>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"367\" height=\"208\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/08\/1004.png\" alt=\"\" class=\"wp-image-2910\" srcset=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/08\/1004.png 367w, https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/08\/1004-300x170.png 300w\" sizes=\"(max-width: 367px) 100vw, 367px\" \/><\/figure>\n\n\n\n<p>The first line returned a 1004 error because Excel contains only 16,384 columns and the last column is XFD. We don\u2019t have the \u201cmyRange\u201d range in the second case. In this case, the application also returns the error. After you create the range, the error won\u2019t show up.<\/p>\n\n\n\n<p>You should really pay attention when you are using loops, especially for columns. You can go out of the index without noticing.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">You are trying to enter a value that is not a range<\/h3>\n\n\n\n<p>The following code will result in an error because the program cannot recognize values inside the <a href=\"https:\/\/officetuts.net\/excel\/vba\/cells-and-range-property\/\">Range property<\/a> as a range.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Sub myFunc()\n    'Error 1004 \"Application-defined or Object-defined error\"\n    Set Rng = Worksheets(\"Sheet1\").Range(4)\n    Set Rng2 = Worksheets(\"Sheet1\").Range(\"4\")\nEnd Sub<\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Select a worksheet explicitly<\/h3>\n\n\n\n<p>Sometimes when you choose a worksheet implicitly, you may have a 1004 error.<\/p>\n\n\n\n<p>Take a look at this code. Here, you selected the active sheet, but when you activate a different sheet this may result in an error.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Set wksSource = ActiveWorkbook.ActiveSheet<\/pre>\n\n\n\n<p>Instead, use the code when you explicitly select the sheet name.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Set wksSource = ActiveWorkbook.Sheets(\"Sheet1\")<\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">You are using the protected mode<\/h3>\n\n\n\n<p>You may also get the 1004 error if your <a href=\"https:\/\/officetuts.net\/excel\/training\/worksheets\/\">worksheet<\/a> is in protected mode. Disabling the protection might get rid of this error.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Macro recorded at the worksheet level<\/h3>\n\n\n\n<p>Sometimes you may get this error if you <a href=\"https:\/\/officetuts.net\/excel\/training\/the-macro-recorder\/\">record a macro<\/a> at the worksheet level. You should create a module and enter the code there. In order to do so, go to VBE (<strong>Alt + F11<\/strong>) and right-click on the module&#8217;s icon. Insert a new module and enter your code there.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"567\" height=\"437\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/08\/vbe.png\" alt=\"\" class=\"wp-image-2909\" srcset=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/08\/vbe.png 567w, https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/08\/vbe-300x231.png 300w\" sizes=\"(max-width: 567px) 100vw, 567px\" \/><\/figure>\n\n\n\n<p>These situations are the most common for error 1004 to occur. I hope it fixed your problem.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>The VBA Runtime Error 1004 can be caused by many things. In this article, I\u2019ll show a few different situations and how to&#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[19],"tags":[190],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v22.3 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>VBA Runtime Error 1004 \u201cApplication-defined or Object-defined error\u201d<\/title>\n<meta name=\"description\" content=\"Troubleshoot and resolve the VBA Runtime Error 1004, &quot;Application-defined or Object-defined error,&quot; in this guide. From referencing non-existent ranges to handling protected worksheets.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/officetuts.net\/excel\/vba\/vba-runtime-error-1004-application-defined-or-object-defined-error\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"VBA Runtime Error 1004 \u201cApplication-defined or Object-defined error\u201d\" \/>\n<meta property=\"og:description\" content=\"Troubleshoot and resolve the VBA Runtime Error 1004, &quot;Application-defined or Object-defined error,&quot; in this guide. From referencing non-existent ranges to handling protected worksheets.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/officetuts.net\/excel\/vba\/vba-runtime-error-1004-application-defined-or-object-defined-error\/\" \/>\n<meta property=\"article:published_time\" content=\"2018-08-11T09:47:43+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-03-29T15:48:43+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/08\/1004.png\" \/>\n<meta name=\"author\" content=\"Tomasz Decker\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Tomasz Decker\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/officetuts.net\/excel\/vba\/vba-runtime-error-1004-application-defined-or-object-defined-error\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/officetuts.net\/excel\/vba\/vba-runtime-error-1004-application-defined-or-object-defined-error\/\"},\"author\":{\"name\":\"Tomasz Decker\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"headline\":\"VBA Runtime Error 1004 \u201cApplication-defined or Object-defined error\u201d\",\"datePublished\":\"2018-08-11T09:47:43+00:00\",\"dateModified\":\"2024-03-29T15:48:43+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/officetuts.net\/excel\/vba\/vba-runtime-error-1004-application-defined-or-object-defined-error\/\"},\"wordCount\":340,\"publisher\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"image\":{\"@id\":\"https:\/\/officetuts.net\/excel\/vba\/vba-runtime-error-1004-application-defined-or-object-defined-error\/#primaryimage\"},\"thumbnailUrl\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/08\/1004.png\",\"keywords\":[\"pinterest\"],\"articleSection\":[\"vba\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/officetuts.net\/excel\/vba\/vba-runtime-error-1004-application-defined-or-object-defined-error\/\",\"url\":\"https:\/\/officetuts.net\/excel\/vba\/vba-runtime-error-1004-application-defined-or-object-defined-error\/\",\"name\":\"VBA Runtime Error 1004 \u201cApplication-defined or Object-defined error\u201d\",\"isPartOf\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/officetuts.net\/excel\/vba\/vba-runtime-error-1004-application-defined-or-object-defined-error\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/officetuts.net\/excel\/vba\/vba-runtime-error-1004-application-defined-or-object-defined-error\/#primaryimage\"},\"thumbnailUrl\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/08\/1004.png\",\"datePublished\":\"2018-08-11T09:47:43+00:00\",\"dateModified\":\"2024-03-29T15:48:43+00:00\",\"description\":\"Troubleshoot and resolve the VBA Runtime Error 1004, \\\"Application-defined or Object-defined error,\\\" in this guide. From referencing non-existent ranges to handling protected worksheets.\",\"breadcrumb\":{\"@id\":\"https:\/\/officetuts.net\/excel\/vba\/vba-runtime-error-1004-application-defined-or-object-defined-error\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/officetuts.net\/excel\/vba\/vba-runtime-error-1004-application-defined-or-object-defined-error\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/officetuts.net\/excel\/vba\/vba-runtime-error-1004-application-defined-or-object-defined-error\/#primaryimage\",\"url\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/08\/1004.png\",\"contentUrl\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/08\/1004.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/officetuts.net\/excel\/vba\/vba-runtime-error-1004-application-defined-or-object-defined-error\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/officetuts.net\/excel\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"VBA Runtime Error 1004 \u201cApplication-defined or Object-defined error\u201d\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/officetuts.net\/excel\/#website\",\"url\":\"https:\/\/officetuts.net\/excel\/\",\"name\":\"\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/officetuts.net\/excel\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\",\"name\":\"Tomasz Decker\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g\",\"caption\":\"Tomasz Decker\"},\"logo\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/\"},\"description\":\"Spreadsheet and Python enthusiast.\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"VBA Runtime Error 1004 \u201cApplication-defined or Object-defined error\u201d","description":"Troubleshoot and resolve the VBA Runtime Error 1004, \"Application-defined or Object-defined error,\" in this guide. From referencing non-existent ranges to handling protected worksheets.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/officetuts.net\/excel\/vba\/vba-runtime-error-1004-application-defined-or-object-defined-error\/","og_locale":"en_US","og_type":"article","og_title":"VBA Runtime Error 1004 \u201cApplication-defined or Object-defined error\u201d","og_description":"Troubleshoot and resolve the VBA Runtime Error 1004, \"Application-defined or Object-defined error,\" in this guide. From referencing non-existent ranges to handling protected worksheets.","og_url":"https:\/\/officetuts.net\/excel\/vba\/vba-runtime-error-1004-application-defined-or-object-defined-error\/","article_published_time":"2018-08-11T09:47:43+00:00","article_modified_time":"2024-03-29T15:48:43+00:00","og_image":[{"url":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/08\/1004.png"}],"author":"Tomasz Decker","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Tomasz Decker","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/officetuts.net\/excel\/vba\/vba-runtime-error-1004-application-defined-or-object-defined-error\/#article","isPartOf":{"@id":"https:\/\/officetuts.net\/excel\/vba\/vba-runtime-error-1004-application-defined-or-object-defined-error\/"},"author":{"name":"Tomasz Decker","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"headline":"VBA Runtime Error 1004 \u201cApplication-defined or Object-defined error\u201d","datePublished":"2018-08-11T09:47:43+00:00","dateModified":"2024-03-29T15:48:43+00:00","mainEntityOfPage":{"@id":"https:\/\/officetuts.net\/excel\/vba\/vba-runtime-error-1004-application-defined-or-object-defined-error\/"},"wordCount":340,"publisher":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"image":{"@id":"https:\/\/officetuts.net\/excel\/vba\/vba-runtime-error-1004-application-defined-or-object-defined-error\/#primaryimage"},"thumbnailUrl":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/08\/1004.png","keywords":["pinterest"],"articleSection":["vba"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/officetuts.net\/excel\/vba\/vba-runtime-error-1004-application-defined-or-object-defined-error\/","url":"https:\/\/officetuts.net\/excel\/vba\/vba-runtime-error-1004-application-defined-or-object-defined-error\/","name":"VBA Runtime Error 1004 \u201cApplication-defined or Object-defined error\u201d","isPartOf":{"@id":"https:\/\/officetuts.net\/excel\/#website"},"primaryImageOfPage":{"@id":"https:\/\/officetuts.net\/excel\/vba\/vba-runtime-error-1004-application-defined-or-object-defined-error\/#primaryimage"},"image":{"@id":"https:\/\/officetuts.net\/excel\/vba\/vba-runtime-error-1004-application-defined-or-object-defined-error\/#primaryimage"},"thumbnailUrl":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/08\/1004.png","datePublished":"2018-08-11T09:47:43+00:00","dateModified":"2024-03-29T15:48:43+00:00","description":"Troubleshoot and resolve the VBA Runtime Error 1004, \"Application-defined or Object-defined error,\" in this guide. From referencing non-existent ranges to handling protected worksheets.","breadcrumb":{"@id":"https:\/\/officetuts.net\/excel\/vba\/vba-runtime-error-1004-application-defined-or-object-defined-error\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/officetuts.net\/excel\/vba\/vba-runtime-error-1004-application-defined-or-object-defined-error\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/officetuts.net\/excel\/vba\/vba-runtime-error-1004-application-defined-or-object-defined-error\/#primaryimage","url":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/08\/1004.png","contentUrl":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/08\/1004.png"},{"@type":"BreadcrumbList","@id":"https:\/\/officetuts.net\/excel\/vba\/vba-runtime-error-1004-application-defined-or-object-defined-error\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/officetuts.net\/excel\/"},{"@type":"ListItem","position":2,"name":"VBA Runtime Error 1004 \u201cApplication-defined or Object-defined error\u201d"}]},{"@type":"WebSite","@id":"https:\/\/officetuts.net\/excel\/#website","url":"https:\/\/officetuts.net\/excel\/","name":"","description":"","publisher":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/officetuts.net\/excel\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42","name":"Tomasz Decker","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g","caption":"Tomasz Decker"},"logo":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/"},"description":"Spreadsheet and Python enthusiast."}]}},"_links":{"self":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/2907"}],"collection":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/comments?post=2907"}],"version-history":[{"count":10,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/2907\/revisions"}],"predecessor-version":[{"id":12706,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/2907\/revisions\/12706"}],"wp:attachment":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/media?parent=2907"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/categories?post=2907"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/tags?post=2907"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}