1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27
|
'****************
' insert grand total column
'****************
iCol = 19 ' S col
Sheets(SHEET_SF).Columns(iCol).Select
Selection.Insert Shift:=xlToRight
Sheets(SHEET_SF).Cells(1, iCol).FormulaR1C1 = "=SUM(RC[-1],RC[-5],RC[-9],RC[-13])"
Sheets(SHEET_SF).Range(Sheets(SHEET_SF).Cells(1, iCol).Address & ":" & Sheets(SHEET_SF).Cells(LastRow, iCol).Address).Select
Selection.FillDown
With Selection.Interior
.ColorIndex = 45
.Pattern = xlSolid
End With
'=SUM(R[-40]C+R[-16]C+R[-14]C+R[-4]C)
'Grand Total
Sheets(SHEET_SF).Cells(LastRow + 2, 2).Value = "Grand Total"
Sheets(SHEET_SF).Cells(LastRow + 2, iCol_Mnth).FormulaR1C1 = sGrandTotalStr
Sheets(SHEET_SF).Range("C" & (LastRow + 2) & ":AY" & (LastRow + 2)).Select
Selection.FillRight
Sheets(SHEET_SF).Range("A" & (LastRow + 2) & ":AY" & (LastRow + 2)).Select
With Selection.Interior
.ColorIndex = 40
.Pattern = xlSolid
End With |
Partager