How to Changing reference columns in another worksheet using VBA looping

How to change reference columns in another worksheet using VBA looping

Requirement:

The user is trying to use a looping SUMIF formula to collect data from one sheet to another and them move along the columns. The formula the user is using for the first column is below. The user is trying to loop through the rows and them move to the next column and go through the same loop (bringing in data over 12 columns.

Code:

Dim counter As Integer

For counter = 1 To 49
'

    ActiveCell.FormulaR1C1 = "=SUMIF('REvenu Data'!C[-1],RC[-1],'REvenu Data'!C)"
    Selection.Offset(1, 0).Select
    
    
    Next counter

https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/1205844-changing-reference-columns-in-another-worksheet-using-vba-looping

Solution:

Code:

Sub TestCoco1921()
Dim i As Integer  ' to define rows
Dim j As Integer  ' to define columns
  
  ' For Rows 3 to 12
  For i = 3 To 12
  ' For Columns B to Z
      For j = 2 To 26
        Cells(i, j).Formula = "=SUMIF('Revenue Data'!C[-1],RC[-1],'Revenue Data'!C)"
      Next j
  Next i
End Sub

f you need a step 2 ... you can test following

Code:

Sub TestCoco1921()
Dim i As Integer  ' to define rows
Dim j As Integer  ' to define columns
  
  ' For Rows 3 to 12
  For i = 3 To 12
  ' For Columns B to AZ with a step 2 ''''''''''''''''''''''''''''''''''''''''''''''
      For j = 2 To 52 Step 2
        Cells(i, j).Formula = "=SUMIF('Revenue Data'!C[-1],RC[-1],'Revenue Data'!C)"
      Next j
  Next i
End Sub

Obtained from the OzGrid Help Forum.

Solution provided by Carim.