Reworking code

[cstimart]

There has to be an easier way, using Range("G9:G13") rather than individual cells...but I can't get it.

    Range("G9").Select
    For i = 0 To 13
        With ActiveCell
            .Offset(0, i) = PivotTable.GetPivotData("Sum of OT Hours", "Pay Period", .Offset(-6, i), "Office", Range("A9")) / PivotTable.GetPivotData("Sum of Total Hours", "Pay Period", .Offset(-6, i), "Office", Range("A9"))
            .Offset(1, i) = PivotTable.GetPivotData("Sum of OT Hours", "Pay Period", .Offset(-6, i), "Office", Range("A10")) / PivotTable.GetPivotData("Sum of Total Hours", "Pay Period", .Offset(-6, i), "Office", Range("A10"))
            .Offset(2, i) = PivotTable.GetPivotData("Sum of OT Hours", "Pay Period", .Offset(-6, i), "Office", Range("A11")) / PivotTable.GetPivotData("Sum of Total Hours", "Pay Period", .Offset(-6, i), "Office", Range("A11"))
            .Offset(3, i) = PivotTable.GetPivotData("Sum of OT Hours", "Pay Period", .Offset(-6, i), "Office", Range("A12")) / PivotTable.GetPivotData("Sum of Total Hours", "Pay Period", .Offset(-6, i), "Office", Range("A12"))
            .Offset(4, i) = PivotTable.GetPivotData("Sum of OT Hours", "Pay Period", .Offset(-6, i), "Office", Range("A13")) / PivotTable.GetPivotData("Sum of Total Hours", "Pay Period", .Offset(-6, i), "Office", Range("A13"))
        End With
    Next i

 

[cstimart]

My thought/wonder is if the code can be reworked removing the .offset(-6,1) and replacing with something like "R3C" ....or does that only work when using Range("G9:T13").FormulaR1C1 = ?

 

[cstimart]

I was able to use the R1C1 style as follows...

Range("G9:T13").FormulaR1C1 = "=GETPIVOTDATA(""Sum of OT Hours"",Pivot!R1C1,""Pay Period"",DATE(YEAR(R3C),MONTH(R3C),DAY(R3C)),""Office"",RC1)/GETPIVOTDATA(""Sum of Total Hours"",Pivot!R1C1,""Pay Period"",DATE(YEAR(R3C),MONTH(R3C),DAY(R3C)),""Office"",RC1)"
Range("G9:T13") = Range("G9:T13").Value

It saves time, but still does some double work by setting the same range twice.

Is it possible to not have to use the second line to put just the value in the cells?