VBA LOOPING FORMULA TREND

[montecarlo2012]

Hello everyone, Hoping you got good weekend and safe.
PLEASE, hard time figuring things out here with looping a formula.
The same formula must be entered in the successive cells columns, the formula must be incremented By one row.

The reading area to apply the formula contains a dynamic array of data, in (“B2:G2800”) at the moment
The formula TREND of each Column in the data BLOCK is to be calculated and entered in the first
8 rows of columns;
if any result match, like B with B or C with C highlight and count beyond column I

CELLS(I3 , N).FORMULA=TRUNC(TREND(B3:B11))
CELLS(I4 , N).FORMULA=TRUNC(TREND(B4:B12))
CELLS(I5 ,N).FORMULA=TRUNC(TREND(B5:B13))
CELLS(I6 , N).FORMULA=TRUNC(TREND(B6:B14)) The difference is 8



In the answer area (“P:N”) the formula would be increase again
CELLS(P3 , U).FORMULA=TRUNC(TREND(B3:B12))
CELLS(I4 , U).FORMULA=TRUNC(TREND(B4:B13))
CELLS(I5 , U).FORMULA=TRUNC(TREND(B5:B14))
CELLS(I6 , U).FORMULA=TRUNC(TREND(B6:B15)) The difference is 9

I want to keep going until the difference is equal to 35
On “I3” you see the number 3 is because three time the numbers on column B, match the results on B.

 

[StephenCrump]

Sub Test()

    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("B3:G2800")
    Set rngStart = Range("I3")
    NoRows = rngData.Rows.Count
    NoCols = 6: Diff1 = 8: Diff2 = 35
    
    For i = Diff1 To Diff2
        rngStart.Offset(, (NoCols + 1) * (i - Diff1)).Resize(NoRows - i, NoCols).Formula = "=TRUNC(TREND(" & rngData.Resize(i + 1, 1).Address(0, 0) & "))"
    Next i

End Sub

 

[StephenCrump]

... will duplicate your results, although I am not clear what you are actually trying to do with TREND()?

 

[montecarlo2012]

... will duplicate your results, although I am not clear what you are actually trying to do with TREND()?

Thank you So much for your time, excellent Answer, as you can see I already CLICK ►LIKE◄ thank you Sir.

 

[montecarlo2012]

The Highlight part I mentioned wasn't take care, but is fine, will be another post I believe.

 

[StephenCrump]

I2: =SUMPRODUCT(--(B3:B2800=I3:I2800))

 

[montecarlo2012]

if any result match, like B with B or C with C highlight and count beyond column I

Sorry. would be very helpful if it is possible to highlight for example if column B=I and C=J etc etc so, at the end everything have been highlight then count on I2, J2, k2 etc the highlights by column, I hope I am saying well, or I am making sense, sorry thank you.

 

[montecarlo2012]

example: I 12 = B12. also I 17 = B17 and I 18=B18 therefore on I 2 shows up number 3 meaning three match,

 

[StephenCrump]

You can use conditional formatting for this.

The revised code below does this, and also adds column totals.

Sub Test()

    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:G2800")
    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

 

[montecarlo2012]

Thank you Sir.