search procedure to addition my code to sum amounts for column in listbox

[Mussa]

Hi
I search for procedure to add to end my code when populate the data in listbox should sum the whole amounts for the column 11 in listbox and show the amounts TOTAL in textbox1 with exclude from summing the lastrow in listbox will contain TOTAL word in first column in listbox .
thanks

 

[Domenic]

To sum Column 11, try...

    With Application
        Me.TextBox1.Value = .Sum(.Index(Me.ListBox1.List, 0, 11))
    End With

If the last row contains the total amount, you can exclude it from the sum as follows...

    Dim i As Long
    Dim total As Double
    
    total = 0#
    With Me.ListBox1
        For i = 0 To .ListCount - 2
            total = total + .List(i, 10)
        Next i
    End With
    
    Me.TextBox1.Value = total

If the first row contains headers, you can also exclude it as follows...

    Dim i As Long
    Dim total As Double
    
    total = 0#
    With Me.ListBox1
        For i = 1 To .ListCount - 2
            total = total + .List(i, 10)
        Next i
    End With
    
    Me.TextBox1.Value = total

Hope this helps!

 

[Mussa]

HI,
all of your suggestion causes mismatch error in theses lines

        Me.TextBox1.Value = .Sum(.Index(Me.ListBox1.List, 0, 11))

            total = total + .List(i, 10)

by the way the column 11 will contains formatting number like this #,##0.00 .
I no know if this is the main reason to show error !

 

[Domenic]

Can you post the code that loads the ListBox with the data?

 

[Mussa]

here is the code

Private Sub UserForm_Initialize()
    Dim lindex&
    Dim rngDB As Range, rng As Range
    Dim i, myFormat(1) As String
    Dim sWidth As String
    Dim vR() As Variant
    Dim n As Integer
    Dim myMax As Single
   
    Dim SH As Worksheet
    ' populate ComboBox1 list eith sheet names
    For Each SH In ThisWorkbook.Worksheets
        ComboBox1.AddItem SH.Name
    Next SH
   
   
    Set rngDB = Range("A1:J20")
    For Each rng In rngDB
        n = n + 1
        ReDim Preserve vR(1 To n)
        vR(n) = rng.EntireColumn.Width
    Next rng
    myMax = WorksheetFunction.Max(vR)
    For i = 1 To n
        vR(i) = myMax
    Next i
    With Sheets("purchase").Cells(1).CurrentRegion
        myFormat(0) = .Cells(2, 8).NumberFormatLocal
        myFormat(1) = .Cells(2, 9).NumberFormatLocal
        Set rng = .Offset(1).Resize(.Rows.Count - 1)
        Arr = .Cells(1).CurrentRegion '.Value
    End With

    sWidth = Join(vR, ";")
    'Debug.Print sWidth
    With ListBox1
        .ColumnCount = 10
        .ColumnWidths = sWidth '<~~ 63;63;63;63;63;63;63;63;63;63;63;63;63;63;63;63;63;63;63;63;63;63;63;63;63;63;63;63
        .List = rng.Value
        .BorderStyle = fmBorderStyleSingle
        For lindex = 0 To .ListCount - 1
            .List(lindex, 0) = (Format((.List(lindex, 0)), "dd/mm/yyyy"))   ' BL = dates
                      '  .List(lindex, 0) = lindex + 1

            .List(lindex, 7) = Format$(.List(lindex, 7), myFormat(0))
            .List(lindex, 8) = Format$(.List(lindex, 8), myFormat(1))
            .List(lindex, 9) = Format$(.List(lindex, 9), myFormat(1))
        Next
      
        Arr = .List
        '<--- this line
    End With
    ' Dim i As Long
    Dim total As Double
   
    total = 0#
    With Me.ListBox1
        For i = 0 To .ListCount - 2
            total = total + .List(i, 9)
        Next i
    End With
   
    Me.TextBox1.Value = total
End Sub

and the data

SSR v0 a modified v0 b.xlsm
	A	B	C	D	E	F	G	H	I	J
1	DATE	CLIENT NO	INVOICE NO	ID-CC	BB-R	TT-Y	OR-GG	QTY	PRICE	TOTAL
2	01/01/2022	CC2-TRB-1	INV-TRGG-1	IDTR-100	FFDOOD-1	PACK-2	TTR	60.00	$120.00	$7,200.00
3	02/01/2022	CC2-TRB-1	INV-TRGG-1	IDTR-101	FFDOOD-2	PACK-3	BBR	10.00	$130.00	$1,300.00
4	03/01/2022	CC2-TRB-1	INV-TRGG-1	IDTR-102	FFDOOD-3	PACK-4	LLR	5.00	$100.00	$500.00
5	04/01/2022	CC2-TRB-1	INV-TRGG-1	IDTR-103	FFDOOD-4	PACK-5	MMR	10.00	$289.00	$2,890.00
6	05/01/2022	CC2-TRB-2	INV-TRGG-2	IDTR-104	FFDOOD-5	PACK-6	ASDR	5.00	$140.00	$700.00
7	06/01/2022	CC2-TRB-2	INV-TRGG-2	IDTR-100	FFDOOD-1	PACK-2	TTR	30.00	$100.00	$3,000.00
8	07/01/2022	CC2-TRB-2	INV-TRGG-2	IDTR-101	FFDOOD-2	PACK-3	BBR	50.00	$120.00	$6,000.00
9	08/01/2022	CC2-TRB-2	INV-TRGG-2	IDTR-107	FFDOOD-6	PACK-7	FFG	12.00	$200.00	$2,400.00
10	09/01/2022	CC2-TRB-2	INV-TRGG-2	IDTR-108	FFDOOD-7	PACK-8	GGF	13.00	$100.00	$1,300.00
11	10/01/2022	CC2-TRB-3	INV-TRGG-3	IDTR-109	FFDOOD-8	PACK-9	GGF	20.00	$110.00	$2,200.00
12	11/01/2022	CC2-TRB-4	INV-TRGG-4	IDTR-110	FFDOOD-9	PACK-10	GGF	30.00	$120.00	$3,600.00
13	12/01/2022	CC2-TRB-5	INV-TRGG-5	IDTR-111	FFDOOD-10	PACK-11	GGF	40.00	$130.00	$5,200.00
14	13/01/2022	CC2-TRB-6	INV-TRGG-6	IDTR-112	FFDOOD-11	PACK-12	GGF	50.00	$140.00	$7,000.00
15	14/01/2022	CC2-TRB-7	INV-TRGG-7	IDTR-113	FFDOOD-12	PACK-13	GGF	60.00	$150.00	$9,000.00
PURCHASE

Cell Formulas
Range	Formula
J2:J15	J2	=H2*I2

with change to column 10.

 

[Domenic]

The Format function uses different formatting strings than the NumberFormat and NumberFormatLocal properties. Have a look at the following article...

Format function (Visual Basic for Applications)

Office VBA reference topic

learn.microsoft.com

So I would suggest that you simply hard-code the correct formatting string for the Format function.