vba nested loop

[montecarlo2012]

Hello , Hoping you got good weekend .
Definitely, I realize this is probably something obvious I'm missing.
For [counter] = [start] to [end]
DO SOMETHING
Next
The same formula must be entered in the successive cells columns, but one of the reference in the formula must be incremented By one row after the all array have been read.
The reading area to apply the formula contains a
DYNAMIC ARRAY
of data, from A4 to the end of G The formula INDEX,LINEST of each Column

Test 1
The interval here is: 8……………..Column I or #9
(B5:B12,$A$4:$A$11^{1,2,3}),1,4))) Results = (I4:N4)
(B6:B13,$A$4:$A$11^{1,2,3}),1,4))) Results = (I5:N5)
(B7:B14,$A$4:$A$11^{1,2,3}),1,4))) Results = (I6:N6)
(B8:B15,$A$4:$A$11^{1,2,3}),1,4))) Results = (I7:N7
(B9:B16,$A$4:$A$11^{1,2,3}),1,4))) Results = (I8:N8)
(B10:B17,$A$4:$A$11^{1,2,3}),1,4))) Results = (I9:N9)

Test 2
The interval here is:9………………… Column P or #16
(B5:B13,$A$4:$A$12^{1,2,3}),1,4))) Results = (P4:U4)
(B6:B14,$A$4:$A$12^{1,2,3}),1,4))) Results = (P5:U5)
(B7:B15,$A$4:$A$12^{1,2,3}),1,4))) Results = (P6:U6)
(B8:B16,$A$4:$A$12^{1,2,3}),1,4))) Results = (P7:U7)
(B9:B17,$A$4:$A$12^{1,2,3}),1,4))) Results = (P8:U8)
(B10:B18,$A$4:$A$12^{1,2,3}),1,4))) Results = (P9:U9)

if any result match, like Pier1 on I, with Pier1 on B or C etc. then highlight and count on I2 and J, and L etc. to the end.
The argumente ,$A$4:$A$11^{1,2,3}),1,4 increase by 1 unit until 35
Workbook is uploaded.
Thank you for reading this post, I really appreciate your kindness.
this is what I expect

This bellow is the wrong result after I tried the loop

and this is the code I work on:

Sub ind()
     Range("I4:N33").Formula = "=trunc(abs(INDEX(LINEST(B5:B12,$A$4:$A$11^{1,2,3}),1,4)))"
End Sub

Sub Mon()
         Dim x As Integer, y As Integer, lastrow As Long
         Dim k As Integer, m As Integer
         
         lastrow = Cells(Rows.Count, 4).End(xlUp).Row
         
                  For x = 4 To lastrow
                              For y = 2 To 7
                                             Cells(x, 7 + y).Formula = "=trunc(abs(INDEX(LINEST(B5:B12,$A$4:$A$11^{1,2,3}),1,4)))"
                              Next y
                  Next x
        
         
                  For k = 4 To lastrow
                              For m = 2 To 7
                                             Cells(k, 14 + m).Formula = "=trunc(abs(INDEX(LINEST(B5:B12,$A$4:$A$12^{1,2,3}),1,4)))"
                              Next m
                  Next k
End Sub

sorry where is the bottom to upload the workbook. sorry.

 

[StephenCrump]

Your immediate problem is that you have different numbers of y values and x values:

LINEST(B5:B12,$A$4:$A$12 ...)

Your code is going to be very slow, because you are writing cell by cell. In each nested loop, you are also writing the same formula into every cell, which I don't think is what you intend?

You could write your formulae more efficiently using code along the lines provided here: VBA LOOPING FORMULA TREND

 

[montecarlo2012]

Thank you StephenCrump. Trend have only one part, index,linest have two parts, so from the other code to this one, I still don't get it. the part $A$4:$A$12 is the hard part to play with.
I tried this one but, I am not good enough

Sub Tt()

    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:G2710") 'Cells(Rows.Count, "A").End(xlUp).Row
    'Set rngData = Cells(Rows.Count, "B").End(xlUp).Row
    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
            .Formula = "=TRUNC(TREND(" & rngData.Resize(i + 1, 1).Address(0, 0) & "))"
            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
this the idea, but with index linest.
thank you Sir.

 

[StephenCrump]

Is this what you need:

Sub Tt()

    Dim rngStart As Range, rngData As Range
    Dim Diff1 As Long, Diff2 As Long, NoRows As Long, NoCols As Long, i As Long
    
    Set rngData = Range("B4:G" & Cells(Rows.Count, "B").End(xlUp).Row)
    NoRows = rngData.Rows.Count
    NoCols = rngData.Columns.Count
    Diff1 = 8: Diff2 = 35
    Set rngStart = Range("I4").Resize(, NoCols)
    
    For i = Diff1 To Diff2
        With rngStart.Offset(, (NoCols + 1) * (i - Diff1)).Resize(NoRows - i)
            .Formula = "=TRUNC(ABS(INDEX(LINEST(" & rngData.Resize(i + 1, 1).Address(0, 0) & "," & rngData.Offset(, -1).Resize(i + 1, 1).Address(0, 1) & "^{1,2,3}),4)))"
        End With
    Next i

End Sub

 

[montecarlo2012]

[SIZE=6]StephenCrump[/SIZE]
You are GREAT, thank you, thank you, work perfect,
You are the BEST.