{"id":398,"date":"2022-08-30T15:00:41","date_gmt":"2022-08-30T15:00:41","guid":{"rendered":"https:\/\/unknownerror.org\/index.php\/2013\/11\/09\/type-mismatch-storing-cell-value-in-vba-array-collection-of-common-programming-errors\/"},"modified":"2022-08-30T15:00:41","modified_gmt":"2022-08-30T15:00:41","slug":"type-mismatch-storing-cell-value-in-vba-array-collection-of-common-programming-errors","status":"publish","type":"post","link":"https:\/\/unknownerror.org\/index.php\/2022\/08\/30\/type-mismatch-storing-cell-value-in-vba-array-collection-of-common-programming-errors\/","title":{"rendered":"type mismatch storing cell value in VBA array-Collection of common programming errors"},"content":{"rendered":"<p><code>MyArr(I)<\/code> will fail, as MyArr has not been defined as an array. Looking at your code, it seems you would like MyArr to contain all the strings found from D1 down to the first empty cell<\/p>\n<pre><code>Dim MyArr\nMyArr=Range(\"D1\", Range(\"D1\").End(xlDown))\nIf VarType(MyArr)&gt;vbArray then 'more than 1 cell returned\n    'D1 is in MyArr(1,1)\n    'D2 is in MyArr(2,1)\n    '...\n    'Lastcell is in MyArr(Ubound(MyArr),1)\nElse 'Only one cell found with text\n    'D1 is in MyArr \n    'note no () -&gt; one cell = no array\nEnd If\n<\/code><\/pre>\n<p id=\"rop\"><small>Originally posted 2013-11-09 18:56:45. <\/small><\/p>","protected":false},"excerpt":{"rendered":"<p>MyArr(I) will fail, as MyArr has not been defined as an array. Looking at your code, it seems you would like MyArr to contain all the strings found from D1 down to the first empty cell Dim MyArr MyArr=Range(&#8220;D1&#8221;, Range(&#8220;D1&#8221;).End(xlDown)) If VarType(MyArr)&gt;vbArray then &#8216;more than 1 cell returned &#8216;D1 is in MyArr(1,1) &#8216;D2 is in [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-398","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/posts\/398","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/comments?post=398"}],"version-history":[{"count":0,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/posts\/398\/revisions"}],"wp:attachment":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/media?parent=398"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/categories?post=398"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/tags?post=398"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}