How to change reference columns in another worksheet using VBA looping
Category: [Excel] Demo Available
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
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.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to use looping to delete cells of similar value |
| How to loop each row if there is data after green colour cell then delete |
| How to loop each row if there is data after green colour cell then delete |
| How to integrate a formula within a loop |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.