Sum macro

lakshmipathi123

Board Regular
Joined
Jul 10, 2012
Messages
52
Office Version
  1. 365
Platform
  1. Windows
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

CostMonthly DeprYtd DeprLtd DeprNBV
608897.1100608897.110
100096.8800100096.880
224448.5600224448.560
60883.220060883.220
411681.8100411681.810
625518.9800625518.980
96220096220
289462.645078.2930469.74142192.15147270.49
348609.926972.241833.2139443.98209165.94
246282.365863.8735183.2276230.3170052.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>
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

hurgadion

Active Member
Joined
Mar 19, 2010
Messages
426
Hi,
try to run the following macro:
Code:
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

Board Regular
Joined
Jul 10, 2012
Messages
52
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jul 10, 2012
Messages
52
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,230
Messages
5,594,947
Members
413,953
Latest member
Arthur1471

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top