VBA Code for Dynamic Total

[Nasmin Saheed]

Hi

I have a dynamic field for get total in my working sheet, Total column is same but row can be variable depending on description include

Example cart is here

[TABLE="class: grid, width: 1163"]
<tbody>[TR]
[TD="align: center"]S.No[/TD]
[TD="align: center"]Description[/TD]
[TD="align: center"]Code[/TD]
[TD="align: center"]Country [/TD]
[TD="align: center"]Ctn[/TD]
[TD="align: center"]Qty[/TD]
[TD="align: center"]Price[/TD]
[TD="align: center"]Amount[/TD]
[TD="align: center"]VAT %[/TD]
[TD="align: center"]VAT Amount[/TD]
[TD="align: center"]Total Amount[/TD]
[TD="align: center"]Gross Weight
(Kgs)[/TD]
[TD="align: center"]Net Weight
(Kgs)[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Item Number - 1[/TD]
[TD="align: center"]1652[/TD]
[TD="align: center"]India[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]15.2812[/TD]
[TD="align: center"] 152.812[/TD]
[TD="align: center"] -[/TD]
[TD="align: center"] -[/TD]
[TD="align: center"] 152.812[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]Item Number - 2[/TD]
[TD="align: center"]1234[/TD]
[TD="align: center"]Korea[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]15.3511[/TD]
[TD="align: center"] 153.511[/TD]
[TD="align: center"] -[/TD]
[TD="align: center"] -[/TD]
[TD="align: center"] 153.511[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]Item Number - 3[/TD]
[TD="align: center"]1234[/TD]
[TD="align: center"]Japan[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]15.2441[/TD]
[TD="align: center"] 152.441[/TD]
[TD="align: center"] -[/TD]
[TD="align: center"] -[/TD]
[TD="align: center"] 152.441[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"] -[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="colspan: 4, align: center"]GRAND TOTAL[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]30[/TD]
[TD="align: center"][/TD]
[TD="align: center"] 458.764 [/TD]
[TD="align: center"][/TD]
[TD="align: center"] - [/TD]
[TD="align: center"] 458.764 [/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]15[/TD]
[/TR]
</tbody>[/TABLE]

I have applied VBA code to get the total as below, total is calculating correctly but once macro is run , if I press F2 can not see the formula, and also I need to get the total with 2 decimal (here I need to apply round formula, I don’t want to apply comma style , )

My coding is below , could you please someone correct me with the correct code what exactly I wanted


Note:- G (column 7) is Ctn
H (column 8) is Qty
J (column 10) is Amount
Etc........

Sheets("Invoice").Range("G" & Sheets("Invoice").Cells(Rows.Count, 7).End(xlUp).Row + 2) = Application.WorksheetFunction.Sum(Sheets("Invoice").Range("G2:G" & Sheets("Invoice").Cells(Rows.Count, 7).End(xlUp).Row))
Sheets("Invoice").Range("H" & Sheets("Invoice").Cells(Rows.Count, 8).End(xlUp).Row + 2) = Application.WorksheetFunction.Sum(Sheets("Invoice").Range("H2:H" & Sheets("Invoice").Cells(Rows.Count, 8).End(xlUp).Row))
Sheets("Invoice").Range("J" & Sheets("Invoice").Cells(Rows.Count, 10).End(xlUp).Row + 2) = Application.WorksheetFunction.Sum(Sheets("Invoice").Range("J2:J" & Sheets("Invoice").Cells(Rows.Count, 10).End(xlUp).Row))
Sheets("Invoice").Range("L" & Sheets("Invoice").Cells(Rows.Count, 12).End(xlUp).Row + 2) = Application.WorksheetFunction.Sum(Sheets("Invoice").Range("L2:L" & Sheets("Invoice").Cells(Rows.Count, 12).End(xlUp).Row))
Sheets("Invoice").Range("M" & Sheets("Invoice").Cells(Rows.Count, 13).End(xlUp).Row + 2) = Application.WorksheetFunction.Sum(Sheets("Invoice").Range("M2:M" & Sheets("Invoice").Cells(Rows.Count, 13).End(xlUp).Row))
Sheets("Invoice").Range("N" & Sheets("Invoice").Cells(Rows.Count, 14).End(xlUp).Row + 2) = Application.WorksheetFunction.Sum(Sheets("Invoice").Range("N2:N" & Sheets("Invoice").Cells(Rows.Count, 14).End(xlUp).Row))
Sheets("Invoice").Range("O" & Sheets("Invoice").Cells(Rows.Count, 15).End(xlUp).Row + 2) = Application.WorksheetFunction.Sum(Sheets("Invoice").Range("O2:O" & Sheets("Invoice").Cells(Rows.Count, 15).End(xlUp).Row))

 

[Fluff]

How about like

With Sheets("Invoice")
   .Range("G" & Rows.Count).End(xlUp).Offset(2).FormulaR1C1 = "=sum(r2c:r[-2]c)"
   .Range("H" & Rows.Count).End(xlUp).Offset(2).FormulaR1C1 = "=sum(r2c:r[-2]c)"
   .Range("J" & Rows.Count).End(xlUp).Offset(2).FormulaR1C1 = "=round(sum(r2c:r[-2]c),2)"
End With

 

[Nasmin Saheed]

How about like

With Sheets("Invoice")
   .Range("G" & Rows.Count).End(xlUp).Offset(2).FormulaR1C1 = "=sum(r2c:r[-2]c)"
   .Range("H" & Rows.Count).End(xlUp).Offset(2).FormulaR1C1 = "=sum(r2c:r[-2]c)"
   .Range("J" & Rows.Count).End(xlUp).Offset(2).FormulaR1C1 = "=round(sum(r2c:r[-2]c),2)"
End With

Hi Sir,

Thanks a lots it is works for me ...

 

[Fluff]

You're welcome & thanks for the feedback