I want to create a custom currency number format only in the total row table if there is a solution and which I mark the yellow color in the screenshot.
Thanks jack
Sub test2()
Dim objListObj As ListObject
Set objListObj = Sheets("test").ListObjects(1)
objListObj.ShowTotals = True
With Sheets("test").ListObjects("Table1")
.ListColumns("Total").TotalsCalculation = xlTotalsCalculationSum
.ListColumns("Pot. :").TotalsCalculation = xlTotalsCalculationSum
.ListColumns("Total End :").TotalsCalculation = xlTotalsCalculationSum
.ListColumns("PRICE2").TotalsCalculation = xlTotalsCalculationSum
End With
End Sub
CodePudding user response:
I recorded a macro and ended up with something like this:
Selection.NumberFormat = "#,###,##0.0000000000000 [$?-x-xbt1]"
How did I find this format? Well, I clicked right in a cell, asked for "Format cells", I chose "Number", "Currency", I chose the one which resembled the most to what I wanted, and clicked on "Custom" in order to see the actual formatting rule, which I can modify afterwards, as in this example:
(The formatting rule, you can see in the "Type" field, gets filled in automatically.)
CodePudding user response:
Excel Table (ListObject
): TotalsCalculation
& TotalsRowRange
Option Explicit
Sub test2()
Dim Headers As Variant
Headers = Array("Total", "Pot. :", "Total End :", "PRICE2")
With ThisWorkbook.Worksheets("test").ListObjects("Table1")
.ShowTotals = True
Dim n As Long
For n = LBound(Headers) To UBound(Headers)
With .ListColumns(Headers(n))
.TotalsCalculation = xlTotalsCalculationSum
.Parent.TotalsRowRange.Columns(.Index) _
.NumberFormat = "#,##0.00 $"
End With
Next n
End With
End Sub