CODEWITHSUNDEEP
excelvba
Mofasa E
Mofasa E

Reputation: 49

Access the array resulting from LinEst function

I am trying to output the values of the R2 from the linEst function for all the rows of data I have using the following code:

Sub getdeflection6()
Dim xvalues() As Double, yvalues() As Double, cell As Range
Dim alldata As Range
Dim results As Variant
Dim counter As Integer

ReDim xvalues(0 To 8, 0 To 0)
ReDim yvalues(0 To 8, 0 To 0)
ReDim results(0 To 5, 0 To 6)

xvalues(0, 0) = 0
xvalues(1, 0) = Range("S2").Value
xvalues(2, 0) = Range("P2").Value
xvalues(3, 0) = Range("M2").Value
xvalues(4, 0) = Range("J2").Value
xvalues(5, 0) = Range("G2").Value
xvalues(6, 0) = Range("C2").Value
xvalues(7, 0) = Range("B2").Value

Set alldata = Range("F7", Range("F7").End(xlDown))

counter = 7
For Each cell In alldata

    yvalues(0, 0) = 0
    yvalues(1, 0) = cell.Offset(0, 15).Value
    yvalues(2, 0) = cell.Offset(0, 12).Value
    yvalues(3, 0) = cell.Offset(0, 9).Value
    yvalues(4, 0) = cell.Offset(0, 6).Value
    yvalues(5, 0) = cell.Offset(0, 3).Value
    yvalues(6, 0) = cell.Value
    yvalues(7, 0) = 0

    results = Application.LinEst(yvalues, Application.Power(xvalues, Array(1, 2, 3, 4, 5)), True, True)

    Cells(counter, 23) = results(2, 0)
    counter = counter + 1

Next cell
End Sub

I am getting an error at the last line when I try to access the results(2,0) array to obtain the R2 value stating the subscript is out of range. Am I doing it wrong? From my understanding the results array will be 5 rows by 6 columns?

Upvotes: 2

Views: 322

Answers (1)

user4039065
user4039065

Reputation:

When you redim results as ReDim results(0 To 5, 0 To 6) it does not 'lock' the LBound and UBound dimensions of that variant array.

This line resets the results into results(1 to 5, 1 to 6).

results = Application.LinEst(yvalues, Application.Power(xvalues, Array(1, 2, 3, 4, 5)), True, True)

In fact, there is no point in the original redim statement at all. Use the new LBound(results, 1) and LBound(results, 2) to retrieve the desired value from the array.

Cells(counter, 23) = results(2, 1)

I'm not sure about the 2 in that. With a new LBound(results, 1) of 1, that might have to be 3.

Upvotes: 1

Related Questions