PDA

View Full Version : [SOLVED] Loop Thru Columns

zoom38
11-17-2017, 06:40 PM
Good evening all, I am trying to loop thru columns changing the cell formulas and am drawing a blank. It seems easy to me but I just can't get it. I'm new to R1C1, tried it but am having difficulties. Please see the comments in the code for my issues. I've searched the net and found plenty of info on R1C1 but cant get it right in my worksheet formulas. Can someone advise where i've gone wrong. Or advise a different way to accomplish this.
My formulas should look something like this:
Cell B5 is =SUM(Jan:June!\$B5)
Cell C5 is =SUM(July:Dec!\$B5) I don't no why the emoji shows here its July-Dec
Cell D5 is =SUM(Jan:June!\$C5)
Cell E5 is =SUM(July:Dec!\$C5) I don't no why the emoji shows here its July-Dec
And so on.

Thanks
Gary

Sub GenerateFirstRowFormulas()
Dim xRow As Integer
Dim yCol As Integer
Dim iCol As Integer
Dim iRow As Integer

ProtectSheet (False)

iRow = 5
xRow = 5
yCol = 0
'Generate the formulas in Row 5
For iCol = 2 To 16
yCol = yCol + 2

'These formulas work except it inserts \$ for absolute references but need relative references for copy routine
Cells(xRow, yCol).FormulaR1C1 = "=SUM('Jan:June'!R" & iRow & "C" & iCol & ")"
Cells(xRow, yCol + 1).FormulaR1C1 = "=SUM('July:Dec'!R" & iRow & "C" & iCol & ")"

'These fomulas use relative references but inserts the wrong row in the formula (row 10 instead of 5)
Cells(xRow, yCol).FormulaR1C1 = "=SUM('Jan:June'!R[" & iRow & "]C" & iCol & ")"
Cells(xRow, yCol + 1).FormulaR1C1 = "=SUM('July:Dec'!R[" & iRow & "]C" & iCol & ")"
Next iCol

ProtectSheet (True)
End Sub

mana
11-17-2017, 07:19 PM
Cells(xRow, yCol).FormulaR1C1 = "=SUM('Jan:June'!RC" & iCol & ")"
Cells(xRow, yCol + 1).FormulaR1C1 = "=SUM('July:Dec'!RC" & iCol & ")"

zoom38
11-17-2017, 07:45 PM
Thank you very much. Works perfectly.