subtraction items between two sheets when show data in listbox on userform

[Mussa]

Hi experts
I hope some body help by adding some lines to achieve my project.
based on the orginal code will populate data in listbox based on sheet PURCHASE and when search item in textbox1 will poulate data based on column D (column(4) in listbox)
now I add another sheet name is SALES when show the data in listbox should subtract the values for columns H,J between sheets(PURCHASE,SALES) based on the item in column D
also when search item in textbox1 based on column D should subtract between two sheets.

search on userform (2) (1).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	1/5/2021	CC2-TRB-1	INV-TRGG-1	IDTR-100	FFDOOD-1	PACK-2	TTR	60.00	120.00	7,200.00
3	1/6/2021	CC2-TRB-1	INV-TRGG-1	IDTR-101	FFDOOD-2	PACK-3	BBR	10.00	130.00	1,300.00
4	1/7/2021	CC2-TRB-1	INV-TRGG-1	IDTR-102	FFDOOD-3	PACK-4	LLR	5.00	100.00	500.00
5	1/8/2021	CC2-TRB-1	INV-TRGG-1	IDTR-103	FFDOOD-4	PACK-5	MMR	10.00	289.00	2,890.00
6	1/9/2021	CC2-TRB-2	INV-TRGG-2	IDTR-104	FFDOOD-5	PACK-6	ASDR	5.00	140.00	700.00
7	1/12/2021	CC2-TRB-2	INV-TRGG-2	IDTR-107	FFDOOD-6	PACK-7	FFG	12.00	200.00	2,400.00
8	1/13/2021	CC2-TRB-2	INV-TRGG-2	IDTR-108	FFDOOD-7	PACK-8	GGF	13.00	100.00	1,300.00
PURCHASE

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

search on userform (2).xlsm
	C	D	E	F	G	H	I	J
1	INVOICE NO	ID-CC	BB-R	TT-Y	OR-GG	QTY	PRICE	TOTAL
2	INV-TRGG-1	IDTR-100	FFDOOD-1	PACK-2	TTR	12.00	122.00	1,464.00
3	INV-TRGG-1	IDTR-101	FFDOOD-2	PACK-3	BBR	5.00	135.00	675.00
4	INV-TRGG-1	IDTR-102	FFDOOD-3	PACK-4	LLR	2.00	110.00	220.00
SALES

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

what I expect in listbox on userform
case1 when run the userform

see the selected items how changes the values in columns 9,10 in listbox , but I don't want to show column price in listbox

when put item in textbox1 based on column D ,also I don't want to show column price in listbox

code

Private Sub TextBox1_Change()
  Call FilterData
End Sub

Private Sub TextBox2_Change()
  
  Call FilterData
 
End Sub

Private Sub TextBox3_Change()

  Call FilterData
  

End Sub

Sub FilterData()
    Dim i As Long, ii As Long, n As Long
    Me.ListBox1.List = a
    If Me.TextBox1 = "" Then Exit Sub
    With Me.ListBox1
        .Clear
        For i = 0 To UBound(a, 1)
            If UCase$(a(i, 3)) Like UCase$(Me.TextBox1) & "*" Then
                .AddItem
                .List(n, 0) = n + 1
                For ii = 1 To UBound(a, 2)
                    .List(n, ii) = a(i, ii)
                Next
                n = n + 1
            End If
        Next
    End With
    Dim r As Long
       Dim MySum, MySum1  As Double

        MySum = 0
        MySum1 = 0
        With ListBox1
            For r = 0 To .ListCount - 1
                MySum = MySum + .List(r, 7)
                MySum1 = MySum1 + .List(r, 9)
            Next r
        End With

        TextBox2.Value = Format(MySum, "#,##0.00")
        TextBox3.Value = Format(MySum1, "#,##0.00")
        
End Sub

Private Sub UserForm_Activate()
      
      

End Sub
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
    Set rngDB = Range("A2: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)
        a = .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
       
        a = .List
        '<--- this line
    End With
End Sub

thanks

 

[Mussa]

hope somebody help or alternitave code .