Dropdown list autocomplete and increment (VBA)

[ssigl]

Hi everyone!

What I am looking to do is create a dropdown that will autosuggest/autocomplete from a list of products in Column A when starting to type in the dropdown. When confirming the product (by pressing enter), I would like if it could automatically reset, but also add an increment of +1 in the cell (in column B) next to the product name.

How would I go about doing this?

Thanks in advance!

 

[MickG]

Try this:-
I have assumed that the "DropDown" is "Combobox1" and it is in a you data sheet, not a "Userform".
Paste this code in the sheet Module.
Right click sheet tab select "View Code"> vbwindow appears> paste at top of Vbwindow > close Vbwindow

Option Explicit
[COLOR=navy]Dim[/COLOR] Dic [COLOR=navy]As[/COLOR] Object
Private [COLOR=navy]Sub[/COLOR] ComboBox1_GotFocus()
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, n [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
        [COLOR=navy]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
            Dic.CompareMode = vbTextCompare
        [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
            [COLOR=navy]Set[/COLOR] Dic(Dn.Value) = Dn
        [COLOR=navy]Next[/COLOR]
[COLOR=navy]With[/COLOR] ComboBox1
    .ListFillRange = ""
    .List = Application.Transpose(Dic.keys)
    .MatchEntry = fmMatchEntryComplete
[COLOR=navy]End[/COLOR] With
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]



Private [COLOR=navy]Sub[/COLOR] ComboBox1_KeyDown(ByVal KeyCode [COLOR=navy]As[/COLOR] MSForms.ReturnInteger, ByVal Shift [COLOR=navy]As[/COLOR] Integer)
    [COLOR=navy]If[/COLOR] KeyCode = 13 [COLOR=navy]Then[/COLOR]
       If ComboBox1.Value <> "" Then
             Dic(ComboBox1.Value).Offset(, 1).Value = Dic(ComboBox1.Value).Offset(, 1).Value + 1
             ComboBox1.ListIndex = -1
      End if [COLOR=navy]
  End[/COLOR] If
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick