Loop is not returning nothing

[montecarlo2012]

Hi.
working with trendline, inserting in a loop, but I don't get any result. I already check Tushar Mehta, and excel tool set for the formulas, the formula I am now is:
Equation: y=c*x^b
c: =EXP(INDEX(LINEST(LN,LN(x),,),1,2))

Sub T_L()
         Dim rngStart As Range, rngData As Range
         Dim Diff1 As Long, Diff2 As Long, NoRows As Long, NoCols As Long, i As Long
         Dim s As String
                  Set rngData = Range("B3:G2720")
                           NoRows = rngData.Rows.Count
                           NoCols = rngData.Columns.Count
                           Diff1 = 8: Diff2 = 35
                  Set rngStart = Range("I3").Resize(, NoCols)
                           For i = Diff1 To Diff2
                                    With rngStart.Offset(, (NoCols + 1) * (i - Diff1)).Resize(NoRows - i)
                                   
                                                         .Rows(0).Formula = "=SUMPRODUCT(--(" & rngData.Resize(NoRows - i, 1).Address(0, 0) & "=" & .Columns(1).Address(0, 0) & "))"
                                                         .Rows(0).Font.Bold = True
                                                         'Equation: y=c*x^b
                                                          'c: =EXP(INDEX(LINEST(LN(y),LN(x),,),1,2))
                                                                              .Formula = "= trunc(abs(EXP(INDEX(LINEST(LN(B3:B19),LN($A$3:$A$19),,),1,2))))"
                                                        
                                                         s = .Cells(1, 1).Address(0, 0)
                                             With .FormatConditions
                                                      .Delete
                                                      .Add Type:=xlExpression, Formula1:="=" & rngData(1, 1).Address(0, 0) & "=" & s
                                                      .Item(1).Interior.Color = vbYellow
                                             End With
                                    End With
                           Next i
End Sub

Thanks.

 

[StephenCrump]

... but I don't get any result.

Your code will give you results. I think you mean the results are not what you want?

'Try replacing
.Formula = "= trunc(abs(EXP(INDEX(LINEST(LN(B3:B19),LN($A$3:$A$19)),2))))"
'with
.Formula = "= trunc(abs(EXP(INDEX(LINEST(LN(" & rngData.Resize(i + 1, 1).Address(0, 0) & "),LN(" & rngData.Offset(, -1).Resize(i + 1, 1).Address(0, 1) & ")),2))))"

 

[montecarlo2012]

StephenCrump, Thank you for your return, I did what you say, and what I get is all the columns with #value! on every single cells.

 

[StephenCrump]

I am assuming from your first post that you have X-values in A3:A2720 and six sets of Y-values in B3:G2720?

 

[montecarlo2012]

Yes Sir. Thank you

 

[montecarlo2012]

Sir. this is the sheet

thank you.

 

[StephenCrump]

OK, let's look at the first formula produced by the code, which should be:

I3: =TRUNC(ABS(EXP(@INDEX(LINEST(LN(B3:B11),LN($A3:$A11)),2))))

What values do you have in A3:A11, and in B3:B11?

 

[StephenCrump]

Sir. this is the sheet

So you are getting results???

What's in Column A?

 

[montecarlo2012]

Column A is empty. and results are #value!

 

[montecarlo2012]

Sorry Sir I see, when I write values on column A then I got results, Ok, Sir. I understand, Sorry. Supposedly I must have values on A because the formula say ($A$3:$A$19),,),1,