I have the following in a spreadsheet for example:

120.82 165.97 27546.65
NA NA NA
99.61 157.86 24920.88
216.03 137.9 19015.91
NA NA NA
NA NA NA
NA NA NA
NA NA NA
NA NA NA
99.61 161.05 25937.26
124.51 193.57 37468.83
99.61 167.15 27938.5
NA NA NA
NA NA NA

These columns are Y, X and X^2 respectively. I ultimately want to use
LINEST() for a quadratic polynomial fit on the rows not displaying NA (the
rows not displaying NA satisfy a previous condition elsewhere on the
spreadsheet). The number of rows with NA will be variable with each different
application of the function. I have written a crude function in VBA using
help from this website and elsewhere, and some basic matlab programming
knowledge, for a straight line fit (i.e. just on Y, X):

Option Base 1

Option Explicit



Function LinReg2(Y, X)

'Take two column vectors, create vba arrays, remove empty values and regress
Y on X

'returning coefficients

Dim ym(), xm()

Dim i, n, j, m As Integer

i = 1

n = 1

j = 1

m = 1

Do Until Y(i, 1) = ""

ReDim Preserve ym(n)

ym(n) = Y(i, 1)

i = i + 1

n = n + 1

Loop

Do Until X(j, 1) = ""

ReDim Preserve xm(m)

xm(m) = X(j, 1)

j = j + 1

m = m + 1

Loop

Dim ym2(), xm2()

Dim b, bb

bb = 1

For b = LBound(ym) To UBound(ym)

If ym(b) < "NA" Then

ReDim Preserve ym2(bb)

ym2(bb) = ym(b)

bb = bb + 1

End If

Next

Dim c, cc

cc = 1

For c = LBound(xm) To UBound(xm)

If xm(c) < "NA" Then

ReDim Preserve xm2(cc)

xm2(cc) = xm(c)

cc = cc + 1

End If

Next

LinReg2 = Application.LinEst(ym2, xm2, True, False)

End Function

However I can't do the same for the quadratic fit [in Excel,
=LINEST(Y,X^{1,2},True,False)] because VBA doesn't allow the abbreviation
X^{1,2}. How can I change my program to prepare Y and X(as an n by 2 matrix
with X in the first column and X^2 in the second) removing the 'NA's as
before? Or alternatively, preparing the three columns as arrays and then
joining the X and X^2 columns before applying the function? I'm very new to
VBA so I'm not very good at array manipulation.

Many thanks,

Sam