Já precisei trabalhar muito com matriz no vba, matriz 4×4 e até 6×6.
Na época relutei muito até ver que não tinha outra alternativa senão usar matriz… Então segue um exemplo de codigo que usa matriz e assim ver que não é um monstro.
 .
Caso tenha outras dúvidas veja o artigo Declaracao-de-matriz

 .

Codigo Matriz No Vba

Sub Macro1()
Dim vetor(5) As String 'declaração de matriz com 6 valores

Sheets("Plan1").Select
Cells(2, 1).Select
linha = 2
conteudo = "vazio"
Do While conteudo <> ""  ' verifica  enquanto linha for diferente de vazia
   vetor(linha – 2) = Cells(linha, 1).Value 'vetor inicia-se com referencia zero
   linha = linha + 1 'contador de linha
   conteudo = Cells(linha, 1).Value 'grava o valor da linha atual
Loop
'apos gravar  os valores na matriz, vamos em outro sheet despejar tudo
'ou seja, melhor q ficar gravando varias variaveis
'ou ficar  utilizando a mesma variavel trocando de sheets

'agora vamos despejar os conteudos sem o while
Sheets("Plan2").Select 'na guia 2
Cells(2, 1).Value = vetor(0)
Cells(3, 1).Value = vetor(1)
Cells(4, 1).Value = vetor(2)
Cells(5, 1).Value = vetor(3)
Cells(6, 1).Value = vetor(4)
Cells(7, 1).Value = vetor(5)
'texto abaixo apenas para demonstrar as alterações
Cells(2, 4).Value = "VOCE ESTÁ NO PLAN2 COM OS VALORES COPIADOS"
ActiveWorkbook.Sheets("Plan1").Tab.ColorIndex = 3 'muda a cor do sheet1 (guia ou plan1)
ActiveWorkbook.Sheets("Plan2").Tab.ColorIndex = 25 'muda a cor do sheet2 (guia ou plan2)

End Sub