LINEST with an autopopulating array within VBA

Hi all,

essentially I am trying to populate an array within a VBA code and then regress the line using the LINEST function

Based on something I saw in one of these forums I tried the following

  Sub LinestTest()
Dim Y As Variant, X As Variant
 
ReDim Y(1 To 3, 1 To 1)
ReDim X(1 To 3, 1 To 1)
 


q = 4
For i = 1 To 3
    Y(i, 1) = q
    q = q + 1
Next


Z = 4
For i = 1 To 3
    X(i, 1) = Z
   Z = Z + 1
Next


 
C1 = Application.WorksheetFunction.Index(Application.WorksheetFunction.LinEst(Y, X ^ [1,2,3,4,5,6]), 1)
 

 
End Sub

however I get a mismatch error can anyone explain what is wrong with the code and what I need to do to extract the regressed coefficients (1-7)