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

 

[Legacy 143009]

Is column E going to show the consumed quantity or remained quantity? In your example, it looks like showing the consumed quantity (for ex.20) but logically it should show remained quantity (30 in my opinion). Can you confirm it please?

 

[Maklil]

Hi

but logically it should show remained quantity (30 in my opinion). Can you confirm it please?

sorry !!
yes you're right about (30 in my opinion)

 

[Legacy 143009]

Then your code should look like this:

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 = ComboBox1.Value And .Cells(i, 3).Value = ComboBox2.Value And .Cells(i, 4).Value = ComboBox3.Value Then
        lValue = .Cells(i, 5).Value - TextBox1.Value
        TextBox2.Value = lValue
        .Cells(lRow + 1, 1).Value = Format(Now(), "dd/mm/yyyy")
        .Cells(lRow + 1, 2).Value = .Cells(i, 2).Value
        .Cells(lRow + 1, 3).Value = .Cells(i, 3).Value
        .Cells(lRow + 1, 4).Value = .Cells(i, 4).Value
        .Cells(lRow + 1, 5).Value = lValue
        Exit For
      End If
    Next
  End With
End Sub

 

[Maklil]

Hi
the code doesn't copy to sheet for separated range , what's my mistake ?

 

[Legacy 143009]

Does the code do anything? Does it copy to somewhere else?
Maybe you should add UserForm1 object:

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
        .Cells(lRow + 1, 1).Value = Format(Now(), "dd/mm/yyyy")
        .Cells(lRow + 1, 2).Value = .Cells(i, 2).Value
        .Cells(lRow + 1, 3).Value = .Cells(i, 3).Value
        .Cells(lRow + 1, 4).Value = .Cells(i, 4).Value
        .Cells(lRow + 1, 5).Value = lValue
        Exit For
      End If
    Next
  End With
End Sub

 

[Maklil]

thanks now I see where is the problem . your code starts copying to the bottom separated ranges , but I want as the picture search for matching for three comboboxes with columns B:D in this case should fill for separated range as in picture 3,5 not copy to the bottom (should n't start after all of separated ranges).

 

[Legacy 143009]

Then maybe something like:

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 + 1).EntireRow.Insert
        .Cells(i + 1, 1).Value = Format(Now(), "dd/mm/yyyy")
        .Cells(i + 1, 2).Value = .Cells(i, 2).Value
        .Cells(i + 1, 3).Value = .Cells(i, 3).Value
        .Cells(i + 1, 4).Value = .Cells(i, 4).Value
        .Cells(i + 1, 5).Value = lValue
        Exit For
      End If
    Next
  End With
End Sub

 

[Maklil]

seem to works very well
just I want when add new row I want adding the borders .I tried with this line

.Rows(i + 1).EntireRow.Insert CopyOrigin:=xlFormatFromRightOrAbove

but doesn't work . any idea?

 

[Legacy 143009]

Yes, because it inserts into one row below. Which is empty.

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).EntireRow.Offset(1, 0).Insert
        .Cells(i + 1, 5).Value = lValue
        Exit For
      End If
    Next
  End With
End Sub