title: Excel VBA - Populate Array with 6 columns, twice
body: <p>I'm working on a macro that will take values from 6 different columns and combine them into one column, then do the same thing with a corresponding values in another 6 columns.</p>
  
  <p>An example to clarify (using 3 columns each instead of 6 for brevity):</p>
  
  <pre><code>|School 1|School 2|School 3|City 1|City 2|City 3|
  |a       |b       |c       |1     |2     |3     |
  </code></pre>
  
  <p>becomes an array with values</p>
  
  <pre><code>array(0,0) = a
  array(1,0) = b
  array(2,0) = 3
  array(0,1) = 1
  array(1,1) = 2
  array(2,1) = 3
  </code></pre>
  
  <p>There are 2000 rows in each column. My current code looks like this.</p>
  
  <pre><code>Sub split_other()
  
  Dim collector(11999, 1) As String
  
  Dim counter1 As Integer
      counter1 = 0
  
  Dim i As Range
  
  Dim Schools As Range
      Set Schools = ActiveWorkbook.Worksheets("Parsing").Range("AB3", "AH2000")
  Dim Cities As Range
      Set Cities = ActiveWorkbook.Worksheets("Parsing").Range("AJ3", "AP2000")
  
  For Each i In Schools
      collector(counter1, 0) = i.Value
      counter1 = counter1 + 1
  Next
  
  For Each i In Cities
      collector(counter1, 1) = i.Value
      counter1 = counter1 + 1
  Next
  
  End Sub
  </code></pre>
  
  <p>The issue I am having is that, by watching the Locals view, I can see that the For Each loops are not populating the array. Additionally, the macro eventually hits a Type mismatch error. The former is the more immediate concern.</p>