Calculate listbox column values

[asyamonique]

Private Sub CommandButton1_Click()



Dim r As Long, Ac As Long, c As Long
  If ComboBox1.Value <> -1 And ComboBox2.Value <> -1 Then
        ListBox1.Clear
        With Worksheets("reports").Range("A1:A1000")
           ReDim Ray(1 To .Count, 1 To 6)
            For r = 1 To .Rows.Count
               With ListBox1
                    .ColumnCount = 6
                    .ColumnWidths = "0;0;80;350;100;100"
                End With
                If ComboBox1.Value = .Cells(r, 1).Value And ComboBox2.Value = .Cells(r, 2).Value Then
                   c = c + 1
                   For Ac = 1 To 6
                        Ray(c, Ac) = .Cells(r, Ac)
                   Next Ac
                End If
            Next r
        End With
End If
ListBox1.list = Ray




End Sub

Hello,
Is it possible add additional code to given above code to show on textbox1 sum of listbox1 5th column vaues?
Many Thanks

 

[dmt32]

try updates to your code shown in red & see if helps you

Private Sub CommandButton1_Click()
    Dim r As Long, Ac As Long, c As Long
    Dim SumValue As Double

    If ComboBox1.Value <> -1 And ComboBox2.Value <> -1 Then
        ListBox1.Clear
        With Worksheets("reports").Range("A1:A1000")
           ReDim Ray(1 To .Count, 1 To 6)
            For r = 1 To .Rows.Count
               With ListBox1
                    .ColumnCount = 6
                    .ColumnWidths = "0;0;80;350;100;100"
                End With
                If ComboBox1.Value = .Cells(r, 1).Value And ComboBox2.Value = .Cells(r, 2).Value Then
                   c = c + 1
                   For Ac = 1 To 6
                   If Ac = 5 Then SumValue = SumValue + Val(.Cells(r, Ac).Value)
                        Ray(c, Ac) = .Cells(r, Ac)
                   Next Ac
                End If
            Next r
        End With
End If
ListBox1.List = Ray

Me.TextBox1.Value = SumValue
End Sub

Dave

 

[MickG]

Another option:-

ListBox1.List = Ray

With Application
   TextBox1.Value = IIf(c > 0, .Sum(.Index(Ray, Evaluate("Row(1:" & c & ")"), 5)), "")
End With

 

[asyamonique]

Thanks a lot...
I did formatted textbox value with below code...

TextBox1.Text = Format(TextBox1.Text, "###,##")

How can I do the same onto listbox?
I want the numbers will populate deciminal when listbox shows all datas...
Thanks

 

[dmt32]

Thanks a lot...
I did formatted textbox value with below code...

TextBox1.Text = Format(TextBox1.Text, "###,##")

How can I do the same onto listbox?
I want the numbers will populate deciminal when listbox shows all datas...
Thanks

Hi,
assuming your cells have been formatted in required manner then try this update to your code

Private Sub CommandButton1_Click()
    Dim r As Long, Ac As Long, c As Long
    Dim SumValue As Double
    Dim rng As Range
    Dim box1 As Object, box2 As Object
    Dim Ray() As Variant
    
    Set box1 = Me.ComboBox1
    Set box2 = Me.ComboBox2
    
    If Not CBool(Len(box1) And Len(box2)) Then Exit Sub
    
    With ThisWorkbook.Worksheets("reports")
        Set rng = .Range(.Range("A2"), .Range("A" & .Rows.Count).End(xlUp))
    End With
    
    With rng
        ReDim Ray(1 To .Count, 1 To 6)
        For r = 1 To .Rows.Count
            If box1.Value = .Cells(r, 1).Value And box2.Value = .Cells(r, 2).Value Then
                c = c + 1
                For Ac = 1 To 6
                    If Ac = 5 Then SumValue = SumValue + Val(.Cells(r, Ac).Value)
                    Ray(c, Ac) = .Cells(r, Ac).Text
                Next Ac
            End If
        Next r
    End With
                
    With ListBox1
        .Clear
        .RowSource = ""
        .ColumnCount = 6
        .ColumnWidths = "0;0;80;350;100;100"
        .List = Ray
    End With
                
    Me.TextBox1.Value = Format(SumValue, "###,##")
End Sub

Dave

 

[asyamonique]

Thank you.