Sum values and divided into textboxes on form based on showing data in listbox

[abdo meghari]

Hello ,
in TB2 will search for BRAND after select optionbutton1,optionutton2 based on sheet name .
so what I want to sum column 5 and put in TB3 , TB4 = sum column 7 and divide on sum column 5 (80,150.00/40.00=2,003.75), TB5= TB3*TB4
and populate column 3 and put in TB1

this is what I have

this is what I want

this code is search BRAND when write in TB2

Option Explicit
Dim a As Variant


Sub FilterData()
  Dim txt1 As String
  Dim i As Long, j As Long, k As Long
  ListBox1.Clear
  ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
  For i = 1 To UBound(a)
    If TextBox2 = "" Then txt1 = a(i, 4) Else txt1 = TextBox2
   
    If LCase(a(i, 4)) Like LCase(txt1) & "*" Then
   
     
      k = k + 1
      For j = 1 To 7
      If j = 1 Then 'Change the 1 in this line for the column number where you have the date
          b(k, j) = Format(a(i, j), "dd/mm/yyyy")
        Else
          b(k, j) = a(i, j)
        End If
       
      Next
    End If
  Next
  If k > 0 Then ListBox1.List = b
  With ListBox1
  .ColumnWidths = "80;80;80;180;100;80;80"
 
        For i = 0 To .ListCount - 1
            .List(i, 4) = Format(.List(i, 4), "#,##0.00")
            .List(i, 5) = Format(.List(i, 5), "#,##0.00")
            .List(i, 6) = Format(.List(i, 6), "#,##0.00")
        Next i
   
  End With
 
 
End Sub


Private Sub OptionButton1_Click()
    If OptionButton1.Value = True And OptionButton2.Value = False Then sheet5.Select
    Call ChangeSheet
    Call FilterData
End Sub

Private Sub OptionButton2_Click()
    If OptionButton2.Value = True And OptionButton1.Value = False Then sheet6.Select
    Call ChangeSheet
    Call FilterData
End Sub

Private Sub TextBox2_Change()
Call FilterData
End Sub




Private Sub ChangeSheet()
    With ActiveSheet
        a = .Range("A2:G" & .Range("D" & Rows.Count).End(3).Row).Value
    End With
End Sub

I hope anybody can help.

 

[Anthony47]

For populating TB3-4 and 5, try with these added lines:

Sub FilterData()
  Dim Tot4 As Double, Tot5 As Double, Tot6 As Double        '++
  Dim txt1 As String
'...
'...
  With Listbox1
  .ColumnWidths = "80;80;80;180;100;80;80"
        For i = 0 To .ListCount - 1
            Tot4 = Tot4 + .List(i, 4)                       '++
            .List(i, 4) = Format(.List(i, 4), "#,##0.00")
            Tot5 = Tot5 + .List(i, 5)                       '++
            .List(i, 5) = Format(.List(i, 5), "#,##0.00")
            Tot6 = Tot6 + .List(i, 6)                       '++
            .List(i, 6) = Format(.List(i, 6), "#,##0.00")
        Next i
  End With
  Me.TextBox3 = Tot4                        '++
  Me.TextBox4 = Tot6 / Tot5                 '++
End Sub

The added lines are marked ++

As fa as TB1 is concerned, I don't seem you explained which is the logic to follow; my best guess is that you want in TB1 the content of third column; so try adding this line:

        If j = 1 Then 'Change the 1 in this line for the column number where you have the date
          b(k, j) = Format(a(i, j), "dd/mm/yyyy")
        Else
          b(k, j) = a(i, j)
          If k = 1 And j = 3 Then Me.TextBox1.Value = a(i, j)       '+++
        End If

Try...

 

[abdo meghari]

thanks Antony for your trying
first it doesn't seem to be correct for price as in picture

second I would get rid of error overflow in this line

 Me.TextBox4 = Tot6 / Tot5                 '++

this occurs when BRAND is error in writing.
thanks again.

 

[Anthony47]

Is that TextBox4, thus populated via Me.TextBox4 = Tot6 / Tot5?
Since Tot5 and Tot6 are dimmed to be a double I expect that Tot6 / Tot5 (and thus the TextBox) should be a number, I cannot decode what is written in that box.
Considering also the Overflow error, try replacing Me.TextBox4 = Tot6 / Tot5 with

If Tot5 > 0 then Me.TextBox4.Value = Format(Tot6 / Tot5, "0.00")

If this doesn't fix the error then we shall investigate