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

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