quantity has to follow the item

[KlausW]

Hi Excel helpers.
I have a Sheets where I order items, I have made a Combo Box where I can search for the items and they appear from cell a9, b9 and down to a.

If I type G in the Combo Box, everything comes with G, and if I type Yeast on English yeast, everything comes with yeast, etc. Pic1 and Pic2

This works really well, now my challenge I want it like this when I type yeast in Combo Box then I write in column C how much yeast I have to order, this quantity has to follow the item . Also, when I search for something else in Combo Box. Pic 3

Item number is in column A, item name is in column B and quantity is in column C, this is the one I enter the quantity I want to order in.

All help will be appreciated.
Regards Klaus W

 

[mumps]

this quantity has to follow the item

Can you explain in detail what you mean by this?

 

[KlausW]

Can you explain in detail what you mean by this?

Hi mumps​

In a Combo Box I write the start letters, or whole word for an item. In column B (starting in B9 and to B3669) the items that starts with the letter or word I write in the Combo box will be show her. If I want to order an item, I type the quantity in column C.

In column A is the items no. and in column B the items name.

Now if I right another letter or word in the Combo box, the new item will be show, but the quantity will not follow the item. But The quantity will still be in column C form the last search.

I make an example.

In the Combo box I type milk, an in column B all the items with milk will be showing. I then order 10 quantities (in column C) of skimmed milk. Next, I type yeast in the Combo box, an in column B all the items with yeast will be showing. I then order 1 quantity (in column C) of yeast. And so on.

When I am finish whit my order, I use the Data > Filter in column C to filter the numbers so I don’t get any cells that is empty or got a zero.

Is it understandable? I have a VBA code that can do a bit the same but I cannot figure out how to fix it so that dove.

KW

 

[mumps]

If you enter a quantity in column C for an item and then enter a different item, the quantity for the previous item will not be visible but it will still be there when you filter the numbers in column C. So I'm not sure what you mean by:

the new item will be show, but the quantity will not follow the item

If you mean the quantity of the previous item, it has been hidden by the new item filter but it has not been deleted.

 

[KlausW]

good evening, the sun has set in denmark, and i have gone to bed. I will write and explain tomorrow. thanks for the help so far. KW

 

[KlausW]

good evening, the sun has set in denmark, and i have gone to bed. I will write and explain tomorrow. thanks for the help so far. KW

Hi again

I will try to explain it with pictures.

Image 1 Combo Box I am looking for the item.

Picture 2 I search for skimmed milk in the Combo box, the result appears in column B and order the number in column C

Picture 3 Now I search for yeast in Combo box, the result appears in column B, now the problem is in column C. The number I ordered of skimmed milk is still there.

Picture 4 Then I order yeast in column C.

Picture 5 When I use Data> filter, only the yeast appears.

Pictures 6 This is how I want it to look like there is both skimmed milk 10 liters and yeast 1 pack.

Picture 7 I was thinking if you could use column P as item number and column R number, which helps columns.

I have a VBA code which almost does it but I cannot figure out how to fix it so it does what I have explained to. I send it along in the hope that it can inspire.

I hope it makes sense.

Greetings from Denmark KW

 

[mumps]

I noticed that "SKIMMED MILK NATURMILK ORG" is on row 9 and that "YEAST 50G" is also on row 9. You entered the quantity "10,00" on row 9 so it still there. How can "SKIMMED MILK NATURMILK ORG" and "YEAST 50G" be on the same row? Also, it is hard to work with pictures. It would be easier see how your data is organized and to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here (de-sensitized if necessary).

 

[KlausW]

Her is the link KW

Order UK

 

[BSALV]

That combobox works already, so if you see your article, goto it in the column B.
i added 2 textboxes for the quantity and a remark.
If you change one of them, the qty or the remark appears in the corresponding column, but is written in "Fra fil" aside your actual data. (columns M:N)

There is 2 named ranges i use, so use the macro "Make_One_Time_Your_Named_ranges" 1 time.
The other macros do what they say
ark1

Private Sub TextBox1_Change()
     My_TextBoxes
End Sub

Private Sub TextBox2_Change()
     My_TextBoxes
End Sub

module7

Sub Make_One_Time_Your_Named_ranges()
     Sheets("prisliste").Range("A8:K3699").Name = "My_Prisliste"
     Sheets("Fra fil").Range("A8:N3699").Name = "My_Fra_fil"
End Sub

Sub List_Empty()

     Set c = Range("My_fra_fil")                                '.
     With Range("My_Fra_fil").Offset(1, 12).Resize(, 2)
          MsgBox .Address                                       'TEMPORARY MESSAGEBOX : just to show you where it works
          .ClearContents                                        'make content empty
     End With
End Sub

Sub My_TextBoxes()
     If StrComp(ActiveSheet.Name, "Prisliste", vbTextCompare) <> 0 Then MsgBox "wrong sheet": Exit Sub     'only in "Prisliste"

     With ActiveCell
          If Intersect(ActiveCell, Range("My_Prisliste").Columns(2).Offset(1)) Is Nothing Then     'check if you point to a certain article in column B
               MsgBox " your active cell isn't in column B", vbInformation: Exit Sub
          End If

          Set c = Sheets("Fra fil").Range("My_fra_fil").Columns(2).Offset(1)     'your names in "Fra fil", 2nd column
          r = Application.Match(.Value, c, 0)                   'find a match
          If IsNumeric(r) Then                                  'found !
               t1 = Ark1.TextBox1.Text                          'textbox quantity
               t2 = Ark1.TextBox2.Text                          'textbox remark
               c.Cells(r, 12).Resize(, 2).Value = Array(CDbl(Replace(Ark1.TextBox1.Text, ".", ",")), Ark1.TextBox2.Text)   'start counting in column 2, as 1st column, the 12th and 13th column are for the quantity and the remark
          Else
               MsgBox "no match found", vbCritical
          End If
     End With
End Sub

Sub List_Autofilter_Ordered_Articles() 'shows all non-zero rows (including errors)
     With Range("My_Data")
     .Application.Goto .Cells(2, 1), 0 'goto the 1st datarow
          .AutoFilter
          .AutoFilter Field:=3, Criteria1:="<>0"                 'all the articles with a non-empty quantity
     End With
End Sub

Sub List_Autofilter_ShowAll()
     With Sheets("prisliste")
          If .FilterMode Then .AutoFilter.ShowAllData
     End With
End Sub

klausW

 

[KlausW]

That combobox works already, so if you see your article, goto it in the column B.
i added 2 textboxes for the quantity and a remark.
If you change one of them, the qty or the remark appears in the corresponding column, but is written in "Fra fil" aside your actual data. (columns M:N)

There is 2 named ranges i use, so use the macro "Make_One_Time_Your_Named_ranges" 1 time.
The other macros do what they say
ark1

Private Sub TextBox1_Change()
     My_TextBoxes
End Sub

Private Sub TextBox2_Change()
     My_TextBoxes
End Sub

module7

Sub Make_One_Time_Your_Named_ranges()
     Sheets("prisliste").Range("A8:K3699").Name = "My_Prisliste"
     Sheets("Fra fil").Range("A8:N3699").Name = "My_Fra_fil"
End Sub

Sub List_Empty()

     Set c = Range("My_fra_fil")                                '.
     With Range("My_Fra_fil").Offset(1, 12).Resize(, 2)
          MsgBox .Address                                       'TEMPORARY MESSAGEBOX : just to show you where it works
          .ClearContents                                        'make content empty
     End With
End Sub

Sub My_TextBoxes()
     If StrComp(ActiveSheet.Name, "Prisliste", vbTextCompare) <> 0 Then MsgBox "wrong sheet": Exit Sub     'only in "Prisliste"

     With ActiveCell
          If Intersect(ActiveCell, Range("My_Prisliste").Columns(2).Offset(1)) Is Nothing Then     'check if you point to a certain article in column B
               MsgBox " your active cell isn't in column B", vbInformation: Exit Sub
          End If

          Set c = Sheets("Fra fil").Range("My_fra_fil").Columns(2).Offset(1)     'your names in "Fra fil", 2nd column
          r = Application.Match(.Value, c, 0)                   'find a match
          If IsNumeric(r) Then                                  'found !
               t1 = Ark1.TextBox1.Text                          'textbox quantity
               t2 = Ark1.TextBox2.Text                          'textbox remark
               c.Cells(r, 12).Resize(, 2).Value = Array(CDbl(Replace(Ark1.TextBox1.Text, ".", ",")), Ark1.TextBox2.Text)   'start counting in column 2, as 1st column, the 12th and 13th column are for the quantity and the remark
          Else
               MsgBox "no match found", vbCritical
          End If
     End With
End Sub

Sub List_Autofilter_Ordered_Articles() 'shows all non-zero rows (including errors)
     With Range("My_Data")
     .Application.Goto .Cells(2, 1), 0 'goto the 1st datarow
          .AutoFilter
          .AutoFilter Field:=3, Criteria1:="<>0"                 'all the articles with a non-empty quantity
     End With
End Sub

Sub List_Autofilter_ShowAll()
     With Sheets("prisliste")
          If .FilterMode Then .AutoFilter.ShowAllData
     End With
End Sub

klausW

Thanks U, I will look at it, an return have nice day KW