Sum macro

[lakshmipathi123]

Hi Experts,

I have query regarding SUM function macro. Would you please help me to provide coding on the following..

1) I a need sum of used range cells from E2 to I2.
2) The SUM total should be after one row but not immediately after the used cells.
3) I need to format SUM totals like.. a) Bold b) separated with Comma c) apply borders
Please refer the below attached excel book for your reference.

Thanks in advance
Lakshmipathi

Cost Monthly Depr Ytd Depr Ltd Depr NBV 608897.11 0 0 608897.11 0 100096.88 0 0 100096.88 0 224448.56 0 0 224448.56 0 60883.22 0 0 60883.22 0 411681.81 0 0 411681.81 0 625518.98 0 0 625518.98 0 9622 0 0 9622 0 289462.64 5078.29 30469.74 142192.15 147270.49 348609.92 6972.2 41833.2 139443.98 209165.94 246282.36 5863.87 35183.22 76230.3 170052.06 =SUM(E2:E11) =SUM(F2:F11) =SUM(G2:G11) =SUM(H2:H11) =SUM(I2:I11) <colgroup><col><col><col><col><col></colgroup><tbody> </tbody>

<tbody>
</tbody>

 

[hurgadion]

Hi,
try to run the following macro:

Sub SumSum()
Dim i&

For i = 5 To 9
  Cells(12, i).Value = Application.Sum(Range(Cells(2, i), Cells(11, i)))
Next i
End Sub

Best regards.

 

[lakshmipathi123]

Hi,

Thanks for your quick reply. Above codings are not working.

1) I a need sum of used range cells from E2 to I2. I mean sum formula should select used range and not F2:F11.
2) The SUM total should be after one row but not immediately after the used cells.
3) I need to format SUM totals like.. a) Bold b) separated with Comma c) apply borders
Please refer the below attached excel book for your reference.

 

[lakshmipathi123]

Hi,

Here is the code

'Do sum Totals of selected category
With Range("E" & Rows.count).End(xlUp).Offset(2, 0).Resize(, 5)
.FormulaR1C1 = "=SUM(R2C:R[-2]C)"
.Font.Bold = True
.Style = "Comma"
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlDouble
.Weight = xlThick
End With
End With