REGLINP or LinEst vba not work corectly

[korneliusz1]

Hi again,

with your help function below work.

But only problem is I have some spreadsheets and they have diffrent number of elements. So I wanted to automatize this but I can't.

This function work
Range("N5").FormulaLocal = "=INDEKS(REGLINP(B2:B184; (A2:A184)^{1;2;3;4;5});1)"


There is 2 try of automatic range where LastRow i index of the last Row. REGLINP is the same that LINEST.

This code is for find c5, c4 ... for knowed data
y = c5 * x ^ 5 + c4 * x ^ 4 + c3 * x ^ 3 + c2 * x ^ 2 + c1 * x + b

If you know some other simpler way to do this please write.


One of this ways is from this forum :https://www.mrexcel.com/forum/excel...c-applications-linest-worksheet-function.html

Dim vCoeff As Variant, vY As Variant, vX As Variant
 
ReDim vY(2 To LastRow, 1 To 1) ' dependent variable with 3 values
ReDim vX(2 To LastRow, 1 To 1)  ' 2 independent variables with 3 values
  
  For r = 2 To LastRow 'LBound(11) 'LastRow ' Or 1 To (Ubound(MyListOfStuff) + 1)


        vX = Cells(r, 1).Value
        vY = Cells(r, 2).Value


 Next r
   
   Dim rY As Range, rX As Range
     Set rX = Range("A2:A" & LastRow)
     Set rY = Range("B2:B" & LastRow)
   
  Range("N5").FormulaLocal = "=INDEKS(REGLINP(rY; rX^{1;2;3;4;5});1)"
   Range("N5").FormulaLocal = "=INDEKS(REGLINP(vY; vX^{1;2;3;4;5});1)"

korneliusz1

 

[wideboydixon]

I'm guessing a bit here but perhaps:

Range("N5").FormulaLocal = "=INDEKS(REGLINP(" & rY.Address & "; " & rX.Address & "^{1;2;3;4;5});1)"

WBD

 

[korneliusz1]

Your proposition make syntax error

Problem is when i write:

 Range("N5").FormulaLocal = "=INDEKS(REGLINP(rY.Adress; rX.Adress^{1;2;3;4;5});1)"

I recive in sheet:

=INDEKS(REGLINP(rY.Adress; rX.Adress^{1;2;3;4;5});1)

I recive text but i would like to recive some data for example B2:B200 insted of rY.Adress

 

[wideboydixon]

You didn't use the same format as I did. My code:

Range("N5").FormulaLocal = "=INDEKS(REGLINP(" & rY.Address & "; " & rX.Address & "^{1;2;3;4;5});1)"

Your code:

Range("N5").FormulaLocal = "=INDEKS(REGLINP(rY.Adress; rX.Adress^{1;2;3;4;5});1)"

You need to use concatenation to construct the formula.

WBD

 

[korneliusz1]

Thank you actually I didn't write .Adress.
Now it works.

Thank you for your help.
Problem is solved