VBA - A line code to manipulate

[montecarlo2012]

Hello.
Trying to explain to myself VBA. I got the anatomy of this code in order to understand How to change or manipulate the for loop results any way I need,

so what I learn from here is:

Cells(Rw, "H").Value = Cells(Rw, "H").Value + Cells(Rw, Cl).Value with this line I can get my result on a column or for me just a vertical answer.

now if I want HORIZONTAL answer I know I can change for this one

Cells(30, Rw).Value = Cells(30, Rw).Value + Cells(Rw, Cl).Value

so I will get all my results in one row, or for me just an horizontal answer.

In this moment my question is:

Rows(0).Formula = "=SUMPRODUCT(--(" & rngData.Resize(NoRows - i, 1).Address(0, 0) & "=" & .Columns(1).Address(0, 0) & "))"

with this line I get horizontal answers, then
how can I change this line to get VERTICAL answer.
Thanks for reading.

 

[rondeondo]

if you mean you want to do something similar with adding up a column then switch the inner and outer for loops.
I may be able to suggest some alternatives if I can understand what you are trying to achieve and what limitations you are trying to overcome. I'm assuming your question is something of a simplification of your actual situation.
If you can share some sample of your data and the desired output someone in the forum may be able to make a more useful suggestion.

 

[montecarlo2012]

Rondeondo
Thank you so much for your offer,
I have a code with a sumproduct formula, and this line display all the results on row 2 starting on column I,
But now would be better for me to be able to see this results in different way, like this:

And this is the code I need help ,

Sub ge_sh()

         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
         
         Application.ScreenUpdating = False
         Application.Calculation = xlManual
         
                  Set rngData = Range("B3:G2720")
                  
                                    NoRows = rngData.Rows.Count
                                    NoCols = rngData.Columns.Count
                                    Diff1 = 6
                                    Diff2 = 60
                                    
                  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 = ture

'*************************************************************************************************************************************************************************************************
' formula ::::   = AVERAGE (B4:B5)
'**************************************************************************************************************************************************************************************************

                                                                           .Formula = "=TRUNC(Average(" & 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
                           
                Application.ScreenUpdating = True
       Application.Calculation = xlAutomatic
       
End Sub

as illustration, this code display something like:

so you can see what I am trying to get on sheet 2 is the same values but in different presentation.
=SUMPRODUCT(--(Sheet1!B3:B2714=Sheet1!I3:I2714)) so now on sheet 2 I write this on B2 another for C2 etc until G =SUMPRODUCT(--(Sheet1!G3:G2714=Sheet1!N3:N2714))
so in order to go down I have to keep going changing each time, for each cell, so you imaging the rest, I still bad about to understand the with----end with and the offset inside and address
thanks again for offer your help.