title: VBA concatenated arrays comparison - error 1004 coming up
body: <p>I have these entries in rows 1 to 10 and columns A to D:</p>
  
  <pre><code>a   10  a   10
  b   20  b   20
  c   30  c   30
  d   40  d   40
  e   50  e   50
  f   60  f   60
  g   70      
  h   80      
  i   90      
  j   100
  </code></pre>
  
  <p>With VBA code (that I will paste below), I am wanting to, using VBA arrays, build a concatenated "virtual column" of A and B and C and D. Then, I am wanting to create a third array that is populated with an array that gives me a MATCH result for each array element using the concatenation of A and B compared to the same of C and D. (There's a point to all this: it's the prototype of something more complex I want to build later on.)</p>
  
  <p>It works up until the 6th element in the array, but beyond that it gives me the 1004 error (with element values after that Empty in the Watch window).</p>
  
  <p>Can anyone give me some pointers why?</p>
  
  <p>Here is the (probably inelegant) VBA code:</p>
  
  <pre><code>Sub concatarray()
  
  Dim count1 As Integer
  Dim count2 As Integer
  count1 = Application.CountA(Columns("A:A"))
  count2 = Application.CountA(Columns("C:C"))
  Dim Column1() As Variant
  Dim Column2() As Variant
  Dim virtColumn3() As Variant
  Dim Column4() As Variant
  Dim Column5() As Variant
  Dim virtColumn6() As Variant
  ReDim virtColumn3(1 To count1, 1 To 1)
  ReDim virtColumn6(1 To count2, 1 To 1)
  Column1 = Range("A1:A" &amp; count1)
  Column2 = Range("B1:B" &amp; count1)
  Column4 = Range("E1:E" &amp; count2)
  Column5 = Range("F1:F" &amp; count2)
  For i = 1 To count1 
      virtColumn3(i, 1) = Column1(i, 1) &amp; Column2(i, 1)
  Next i
  For i = 1 To count2
      virtColumn6(i, 1) = Column4(i, 1) &amp; Column5(i, 1)
  Next i
  Dim virtColumn7() As Variant
  ReDim virtColumn7(1 To count1, 1 To 1)
  For i = 1 To count1
      virtColumn7(i, 1) = WorksheetFunction.Match(virtColumn3(i, 1), virtColumn6, 0)
  Next i
  
  End Sub
  </code></pre>
  
  <p>It's almost definitely this snippet that's wrong:</p>
  
  <pre><code> For i = 1 To count1
          virtColumn7(i, 1) = WorksheetFunction.Match(virtColumn3(i, 1), virtColumn6, 0)
   Next i
  </code></pre>
  
  <p>Thanks in advance.</p>