Excel VBA - Populate Array with 6 columns, twice

Question

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.

An example to clarify (using 3 columns each instead of 6 for brevity):

|School 1|School 2|School 3|City 1|City 2|City 3|
|a       |b       |c       |1     |2     |3     |

becomes an array with values

array(0,0) = a
array(1,0) = b
array(2,0) = 3
array(0,1) = 1
array(1,1) = 2
array(2,1) = 3

There are 2000 rows in each column. My current code looks like this.

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

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.

@BruceWayne counter1 = counter1 + 1 is iterating the counter and i is a range not a number. — Scott Craner
Personally I would load both ranges into another array and iterate the array instead of making 10's of thousands calls to the worksheet. — Scott Craner
@ScottCraner facepalm, of course...wow I wasn't thinking earlier, sorry! — BruceWayne

Scott Craner Scott Craner · Accepted Answer · 2017-12-01T20:24:28

Just assign the values directly to the array:

Sub split_other()

Dim collector() As Variant

With ActiveWorkbook.Worksheets("Sheet1")
    ReDim collector(1 To .Range("AB3", "AH2000").Cells.Count, 1 To 2) As Variant
    Dim counter1 As Integer
        counter1 = 1

    Dim i As Variant

    Dim Schools() As Variant
        Schools = .Range("AB3", "AH2000").Value
    Dim Cities() As Variant
        Cities = .Range("AJ3", "AP2000").Value


    For Each i In Schools
        collector(counter1, 1) = i
        counter1 = counter1 + 1
    Next

    counter1 = 1

    For Each i In Cities
        collector(counter1, 2) = i
        counter1 = counter1 + 1
    Next

    .Range("AQ3").Resize(UBound(collector, 1), UBound(collector, 2)).Value = collector
End With
End Sub

Excel VBA - Populate Array with 6 columns, twice

1 Answers