# match based on three comboboxes  for  multiple separated range from userform



## Maklil (Dec 17, 2022)

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.xlsmABCDE1DATECUSTOMERINV NOITEMQTY211/11/2022CSS-100INV-A123ITTT-100/AS-110034DATECUSTOMERINV NOITEMQTY511/11/2022CSS-100INV-A123ITTT-100/AS-220067DATECUSTOMERINV NOITEMQTY811/11/2022CSS-102INV-A125ITTT-100/AS-2300910DATECUSTOMERINV NOITEMQTY1111/11/2022CSS-103INV-A126ITTT-100/AS-21001213DATECUSTOMERINV NOITEMQTY1411/11/2022CSS-100INV-A124ITTT-100/AS-22001516DATECUSTOMERINV NOITEMQTY1711/11/2022CSS-105INV-A128ITTT-100/AS-64001819DATECUSTOMERINV NOITEMQTY2011/11/2022CSS-100INV-A129ITTT-100/AS-7300DETAILS



fill the  userform





copy  to sheet  for  separated range
MAKLIL.xlsmABCDE1DATECUSTOMERINV NOITEMQTY211/11/2022CSS-100INV-A123ITTT-100/AS-1100317/12/2022CSS-100INV-A123ITTT-100/AS-150DETAILS

another process

fill userform







copy to sheet
MAKLIL.xlsmABCDE1DATECUSTOMERINV NOITEMQTY211/11/2022CSS-100INV-A123ITTT-100/AS-1100317/12/2022CSS-100INV-A123ITTT-100/AS-150417/12/2022CSS-100INV-A123ITTT-100/AS-120DETAILS


----------



## Flashbond (Dec 19, 2022)

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 (Dec 19, 2022)

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)


----------



## Flashbond (Dec 19, 2022)

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 (Dec 19, 2022)

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


----------



## Flashbond (Dec 19, 2022)

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 (Dec 23, 2022)

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).


----------



## Flashbond (Dec 23, 2022)

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 (Dec 24, 2022)

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?


----------



## Flashbond (Dec 24, 2022)

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
```


----------



## Maklil (Dec 17, 2022)

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.xlsmABCDE1DATECUSTOMERINV NOITEMQTY211/11/2022CSS-100INV-A123ITTT-100/AS-110034DATECUSTOMERINV NOITEMQTY511/11/2022CSS-100INV-A123ITTT-100/AS-220067DATECUSTOMERINV NOITEMQTY811/11/2022CSS-102INV-A125ITTT-100/AS-2300910DATECUSTOMERINV NOITEMQTY1111/11/2022CSS-103INV-A126ITTT-100/AS-21001213DATECUSTOMERINV NOITEMQTY1411/11/2022CSS-100INV-A124ITTT-100/AS-22001516DATECUSTOMERINV NOITEMQTY1711/11/2022CSS-105INV-A128ITTT-100/AS-64001819DATECUSTOMERINV NOITEMQTY2011/11/2022CSS-100INV-A129ITTT-100/AS-7300DETAILS



fill the  userform





copy  to sheet  for  separated range
MAKLIL.xlsmABCDE1DATECUSTOMERINV NOITEMQTY211/11/2022CSS-100INV-A123ITTT-100/AS-1100317/12/2022CSS-100INV-A123ITTT-100/AS-150DETAILS

another process

fill userform







copy to sheet
MAKLIL.xlsmABCDE1DATECUSTOMERINV NOITEMQTY211/11/2022CSS-100INV-A123ITTT-100/AS-1100317/12/2022CSS-100INV-A123ITTT-100/AS-150417/12/2022CSS-100INV-A123ITTT-100/AS-120DETAILS


----------



## Maklil (Dec 24, 2022)

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


----------



## Flashbond (Dec 24, 2022)

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 (Dec 25, 2022)

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


----------



## Flashbond (Dec 25, 2022)

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 (Dec 26, 2022)

> Actually I forgot the timestamp


I don't  note  it  ! 
thanks  for  correction .


----------

