title: Why am I having issues assigning a Range to an Array of Variants
body: <p>I am having a few problems with some VERY simple lines of code. Let me detail the facts and see if anyone else can replicate this behavior. If any one can replicate I would like to get an explanation of why it is happening.</p>
  
  <p>So lets me start with a very simple line of code THAT WORKS:</p>
  
  <pre><code>Dim arr() As Variant
  arr = Range("A1:A10")
  </code></pre>
  
  <p>this does as expected, <code>arr</code> is assigned the Values of <code>A1:A10</code></p>
  
  <p>now why won't the following line of code work?</p>
  
  <pre><code>Dim arr() As Variant
  arr = WorkSheets("Sheet1").Range("A1:A10")
  </code></pre>
  
  <p>I get a Run-Time Error '13' Type mismatch, even though the same range was successfully assigned to the array, just without the Worksheet value. </p>
  
  <p>But </p>
  
  <pre><code>Dim arr As Variant
  arr = Worksheets("Sheet1").Range("A1:A10")
  </code></pre>
  
  <p>And </p>
  
  <pre><code>Dim arr() As Variant
  arr = Application.Transpose(Application.Transpose(Worksheets("Sheet1").Range("A1:A10")))
  </code></pre>
  
  <p>DOES WORK</p>
  
  <p>Now before you answer please let me give you some more facts.</p>
  
  <pre><code>Dim arr() As Variant
  arr = Worksheets(1).Range("A1:A10")
  </code></pre>
  
  <p>Does Not Work</p>
  
  <p>and using <code>Sheets</code> in place of <code>Worksheets</code> also all give the same error.</p>
  
  <p>I have made sure it is the same sheet as the active referenced sheet by using <code>Range("A1:A10").Worksheet.Name</code> Following the working code and it indeed says <code>Sheet1</code> in the output. </p>
  
  <p>No other workbooks are open so it can't be referencing another workbook either.</p>
  
  <p>Now this last bit of code only adds to my confusion as it totally works!</p>
  
  <pre><code>Dim arr() As Variant
  Dim SampleRange As Range
  
  Set SampleRange = Worksheets("Sheet1").Range("A1:A10")
  arr = SampleRange
  </code></pre>
  
  <p>So using the SAME RANGE defined the same way on the same sheet now works when I assign it to a Range Variable. and use that! And as expected this works with both the <code>WorkSheets</code> and <code>Sheets</code> function regardless of how I define the sheet (I can use the index or the Name of the worksheet and all work fine)</p>
  
  <p>If it helps anyone, I am testing this with Excel 2007 on a Windows XP machine. I have not yet tested it on any other machines but I plan to test on 2003 and 2010 on Windows 7 and 8, just haven't had the chance yet.</p>
  
  <p><em><strong>UPDATE:</em></strong> Not 100% sure if this is the same exact issue as with the array but from a shallow view it seems to be: </p>
  
  <pre><code> Range("B1:B3") = Range("A1:A3") 
  </code></pre>
  
  <p>The above code will not work, even if A1:A3 is populated, dates, numeric values, strings, formula anything, it will write blanks into B1:B3</p>
  
  <p>But </p>
  
  <pre><code>Range("B1:B3").Value = Range("A1:A3").Value
  </code></pre>
  
  <p>And </p>
  
  <pre><code>Range("B1") = Range("A1")
  </code></pre>
  
  <p><em>does</em> work!</p>
  
  <p>Also working is: </p>
  
  <pre><code>Range("B1:B3") = Application.Transpose(Application.Transpose(Range("A1:A3")))
  </code></pre>