match based on three comboboxes for multiple separated range from userform

[Maklil]

Hi experts ,
I need matchin combobox1,2,3 with columns B:D for sheet . I want when fill data on userform combobox1,2,3 ,textbox1, then will copy to separated range is matched with combobox1,2,3 , as to textbox2 just I want showing remaining values , it depends on subtraction the value in first row from others values under first row , for instance if I write 50 in textbox1 , then textbox2 will populate value from sheet is 100- 50 which fill in textbox2 then will show 50 in textbox2 as in pic2 , if I write 20 in textbox1 then textbox2 will populate value from sheet is 100- (50+20) which fill in textbox2 then will show 30 in textbox2 as in pic4 so when show the values in textbox2 depends on first row contains value in column E subtract from aggregate under firs row .
when copy data to sheet should show the date today in column A .

MAKLIL.xlsm
	A	B	C	D	E
1	DATE	CUSTOMER	INV NO	ITEM	QTY
2	11/11/2022	CSS-100	INV-A123	ITTT-100/AS-1	100
3
4	DATE	CUSTOMER	INV NO	ITEM	QTY
5	11/11/2022	CSS-100	INV-A123	ITTT-100/AS-2	200
6
7	DATE	CUSTOMER	INV NO	ITEM	QTY
8	11/11/2022	CSS-102	INV-A125	ITTT-100/AS-2	300
9
10	DATE	CUSTOMER	INV NO	ITEM	QTY
11	11/11/2022	CSS-103	INV-A126	ITTT-100/AS-2	100
12
13	DATE	CUSTOMER	INV NO	ITEM	QTY
14	11/11/2022	CSS-100	INV-A124	ITTT-100/AS-2	200
15
16	DATE	CUSTOMER	INV NO	ITEM	QTY
17	11/11/2022	CSS-105	INV-A128	ITTT-100/AS-6	400
18
19	DATE	CUSTOMER	INV NO	ITEM	QTY
20	11/11/2022	CSS-100	INV-A129	ITTT-100/AS-7	300
DETAILS

fill the userform

copy to sheet for separated range

MAKLIL.xlsm
	A	B	C	D	E
1	DATE	CUSTOMER	INV NO	ITEM	QTY
2	11/11/2022	CSS-100	INV-A123	ITTT-100/AS-1	100
3	17/12/2022	CSS-100	INV-A123	ITTT-100/AS-1	50
DETAILS

another process

fill userform

copy to sheet

MAKLIL.xlsm
	A	B	C	D	E
1	DATE	CUSTOMER	INV NO	ITEM	QTY
2	11/11/2022	CSS-100	INV-A123	ITTT-100/AS-1	100
3	17/12/2022	CSS-100	INV-A123	ITTT-100/AS-1	50
4	17/12/2022	CSS-100	INV-A123	ITTT-100/AS-1	20
DETAILS

 

[Maklil]

I no know if you understand me . your code just adds the values for textbox2 and ignores comboboxes .

what I want when add new row should be with the same borders as in previous row

 

[Legacy 143009]

Thats all I can understand. Sorry.

Sub addItem()
  Dim lRow As Long
  Dim lValue As Variant

  lRow = Worksheets("DETAILS").Cells(Rows.Count, 1).End(xlUp).Row
  With Worksheets("DETAILS")
    For i = lRow To 2 Step-1
      If .Cells(i, 2).Value = UserForm1.ComboBox1.Value And .Cells(i, 3).Value = UserForm1.ComboBox2.Value And .Cells(i, 4).Value = UserForm1.ComboBox3.Value Then
        lValue = .Cells(i, 5).Value - UserForm1.TextBox1.Value
        UserForm1.TextBox2.Value = lValue
        .Rows(i).Copy
        .Rows(i + 1). EntireRow.Insert
        .Cells(i + 1, 5).Value = lValue
        Exit For
      End If
    Next
  End With
End Sub

 

[Maklil]

awesome !!
this really works
much appreciated for your time and assistance

 

[Legacy 143009]

Actually I forgot the timestamp. Here is the final version:

Sub addItem()
  Dim lRow As Long
  Dim lValue As Variant

  lRow = Worksheets("DETAILS").Cells(Rows.Count, 1).End(xlUp).Row
  With Worksheets("DETAILS")
    For i = lRow To 2 Step-1
      If .Cells(i, 2).Value = UserForm1.ComboBox1.Value And .Cells(i, 3).Value = UserForm1.ComboBox2.Value And .Cells(i, 4).Value = UserForm1.ComboBox3.Value Then
        lValue = .Cells(i, 5).Value - UserForm1.TextBox1.Value
        UserForm1.TextBox2.Value = lValue
        .Rows(i).Copy
        .Rows(i + 1). EntireRow.Insert
        .Cells(i + 1, 1).Value = Format(Now(), "dd/mm/yyyy")
        .Cells(i + 1, 5).Value = lValue
        Exit For
      End If
    Next
  End With
End Sub

 

[Maklil]

Actually I forgot the timestamp

I don't note it !
thanks for correction .