I am trying to reference a cell in a formula using RC[-x] to get some value. The problem is that the x will be changing, because it is a growing file overtime, so columns will be added always.
I can find a Cell above the one I want to get the value of. But how to get the value of the cell I need, in a dynamic formula using RC[-x]? See Screenshot for more clarification.
So the Formula will be something like this:
Range(ActiveCell, ActiveCell.End(xlDown)).FormulaR1C1 = "=RC[-4]-RC[-14]"
The -4 will be always in that position, and won’t be moving. But the -14 would be changing in future file versions. I can go to cell above that, which is “FC Jun 2024” using:
ActiveSheet.PivotTables("GattungLevel").PivotFields("FC Stand").PivotItems("FC Jun 2024").LabelRange.Select
But how to reference the cell below it and get it through RC[-x] form?
1
I got it resolved through this solution:
<code>ActiveSheet.PivotTables("GattungLevel").PivotFields("FC Stand").PivotItems("FC Jul 2024").LabelRange.Select
Selection.Offset(1, 4).Select
Dim colDiff As Integer
colDiff = ActiveSheet.PivotTables("GattungLevel").PivotFields("FC Stand").PivotItems("FC Jun 2024").LabelRange.Column - _
ActiveSheet.PivotTables("GattungLevel").PivotFields("FC Stand").PivotItems("FC Jul 2024").LabelRange.Column
Range(ActiveCell, ActiveCell.End(xlDown)).FormulaR1C1 = "=RC[-4]-RC[" & (-4 + colDiff) & "]"
</code>
<code>ActiveSheet.PivotTables("GattungLevel").PivotFields("FC Stand").PivotItems("FC Jul 2024").LabelRange.Select
Selection.Offset(1, 4).Select
Dim colDiff As Integer
colDiff = ActiveSheet.PivotTables("GattungLevel").PivotFields("FC Stand").PivotItems("FC Jun 2024").LabelRange.Column - _
ActiveSheet.PivotTables("GattungLevel").PivotFields("FC Stand").PivotItems("FC Jul 2024").LabelRange.Column
Range(ActiveCell, ActiveCell.End(xlDown)).FormulaR1C1 = "=RC[-4]-RC[" & (-4 + colDiff) & "]"
</code>
ActiveSheet.PivotTables("GattungLevel").PivotFields("FC Stand").PivotItems("FC Jul 2024").LabelRange.Select
Selection.Offset(1, 4).Select
Dim colDiff As Integer
colDiff = ActiveSheet.PivotTables("GattungLevel").PivotFields("FC Stand").PivotItems("FC Jun 2024").LabelRange.Column - _
ActiveSheet.PivotTables("GattungLevel").PivotFields("FC Stand").PivotItems("FC Jul 2024").LabelRange.Column
Range(ActiveCell, ActiveCell.End(xlDown)).FormulaR1C1 = "=RC[-4]-RC[" & (-4 + colDiff) & "]"