1. ## Loop Thru Columns

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 ec!\$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 ec!\$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```  Reply With Quote

2. ```        Cells(xRow, yCol).FormulaR1C1 = "=SUM('Jan:June'!RC" & iCol & ")"
Cells(xRow, yCol + 1).FormulaR1C1 = "=SUM('July:Dec'!RC" & iCol & ")"```  Reply With Quote

3. Thank you very much. Works perfectly.  Reply With Quote

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•