Nasmin Saheed
New Member
- Joined
- Jun 11, 2015
- Messages
- 49
- Office Version
- 365
- Platform
- Windows
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
<tbody>
</tbody>
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))
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
S.No | Description | Code | Country | Ctn | Qty | Price | Amount | VAT % | VAT Amount | Total Amount | Gross Weight (Kgs) | Net Weight (Kgs) |
1 | Item Number - 1 | 1652 | India | 2 | 10 | 15.2812 | 152.812 | - | - | 152.812 | 6 | 5 |
2 | Item Number - 2 | 1234 | Korea | 2 | 10 | 15.3511 | 153.511 | - | - | 153.511 | 6 | 5 |
3 | Item Number - 3 | 1234 | Japan | 2 | 10 | 15.2441 | 152.441 | - | - | 152.441 | 6 | 5 |
- | ||||||||||||
GRAND TOTAL | 6 | 30 | 458.764 | - | 458.764 | 18 | 15 |
<tbody>
</tbody>
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))
Last edited: