'Excel VBA Match-Lookup with numbers

I am trying to make a form, where you can input and change database entries. Every can be searched by Name or ID. The form looks like this: Form

So if I enter a name, this code will autofill all other boxes.

Private Sub ComboBox1_Change()
    Dim i As Long
        If Not Comb_Arrow Then
        With Me.ComboBox1
            .List = Worksheets("Datenbank").Range("D4", Worksheets("Datenbank").Cells(Rows.Count, "D").End(xlUp)).Value
            .ListRows = Application.WorksheetFunction.Min(4, .ListCount)
            .DropDown
               If Len(.Text) Then
                    For i = .ListCount - 1 To 0 Step -1
                       If InStr(1, .List(i), .Text, vbTextCompare) = 0 Then .RemoveItem i
                         Next
                          .DropDown
                           End If
         End With
         End If

If Me.ComboBox1.Value = "" Then
    Exit Sub
Else
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Datenbank")
Dim Selected_Row As Integer
On Error Resume Next
Selected_Row = Application.WorksheetFunction.Match(Me.ComboBox1.Value, sh.Range("D:D"), 0)
Worksheets("Datenbank").Cells(1, 1).Value = Me.ComboBox1.Value
Me.ComboBox2.Value = Worksheets("Datenbank").Cells(Selected_Row, 6).Value
Me.ComboBox3.Value = Worksheets("Datenbank").Cells(Selected_Row, 8).Value
Me.ComboBox4.Value = Worksheets("Datenbank").Cells(Selected_Row, 10).Value
Me.ComboBox5.Value = Worksheets("Datenbank").Cells(Selected_Row, 12).Value
Me.ComboBox6.Value = Worksheets("Datenbank").Cells(Selected_Row, 14).Value
Me.ComboBox7.Value = Worksheets("Datenbank").Cells(Selected_Row, 16).Value
Me.ComboBox8.Value = Worksheets("Datenbank").Cells(Selected_Row, 18).Value
Me.TextBox15.Value = Worksheets("Datenbank").Cells(Selected_Row, 7).Value
Me.TextBox16.Value = Worksheets("Datenbank").Cells(Selected_Row, 9).Value
Me.TextBox17.Value = Worksheets("Datenbank").Cells(Selected_Row, 11).Value
Me.TextBox18.Value = Worksheets("Datenbank").Cells(Selected_Row, 13).Value
Me.TextBox19.Value = Worksheets("Datenbank").Cells(Selected_Row, 15).Value
Me.TextBox20.Value = Worksheets("Datenbank").Cells(Selected_Row, 17).Value
Me.TextBox21.Value = Worksheets("Datenbank").Cells(Selected_Row, 19).Value
Me.TextBox22.Value = Worksheets("Datenbank").Cells(Selected_Row, 5).Value
Me.ComboBox9.Value = Worksheets("Datenbank").Cells(Selected_Row, 5).Value
End If

I tried a similar code for the one for the ID, but it only works if the ID contains a normal letter. So how do I get the Match() function to work. I tried following:

converting the value into a string

Selected_Row = Application.WorksheetFunction.Match(CStr(ComboBox9.Value), sh.Range("E:E"), 0)

putting the Code into a separate, as text formatted cell

Worksheets("Dashboard").Cells(7, "R").Value = ComboBox9.Value
Selected_Row = Application.WorksheetFunction.Match(Worksheets("Dashboard").Cells(7, "R"), sh.Range("E:E"), 0)

I'm clueless why it isn't working. On my Dashboard it is also working fine with this code:

Worksheets("Dashboard").Cells(7, "R").Value = ComboBox2.Value
Selected_Row = Application.WorksheetFunction.Match(Worksheets("Dashboard").Cells(7, "R"), sh.Range("E:E"), 0)

The form has two search options: a) after ID and b) after name. Name works fine and the match-function does that now too. form But now I am getting runtime errors, allthough the filling function is the same as in the one for the name combobox which works completely fine.


Me.ComboBox2.Value = Worksheets("Datenbank").Cells(Selected_Row, 6).Value
Me.ComboBox3.Value = Worksheets("Datenbank").Cells(Selected_Row, 8).Value
Me.ComboBox4.Value = Worksheets("Datenbank").Cells(Selected_Row, 10).Value
Me.ComboBox5.Value = Worksheets("Datenbank").Cells(Selected_Row, 12).Value
Me.ComboBox6.Value = Worksheets("Datenbank").Cells(Selected_Row, 14).Value
Me.ComboBox7.Value = Worksheets("Datenbank").Cells(Selected_Row, 16).Value
Me.ComboBox8.Value = Worksheets("Datenbank").Cells(Selected_Row, 18).Value
Me.TextBox15.Value = Worksheets("Datenbank").Cells(Selected_Row, 7).Value
Me.TextBox16.Value = Worksheets("Datenbank").Cells(Selected_Row, 9).Value
Me.TextBox17.Value = Worksheets("Datenbank").Cells(Selected_Row, 11).Value
Me.TextBox18.Value = Worksheets("Datenbank").Cells(Selected_Row, 13).Value
Me.TextBox19.Value = Worksheets("Datenbank").Cells(Selected_Row, 15).Value
Me.TextBox20.Value = Worksheets("Datenbank").Cells(Selected_Row, 17).Value
Me.TextBox21.Value = Worksheets("Datenbank").Cells(Selected_Row, 19).Value
Me.TextBox22.Value = Worksheets("Datenbank").Cells(Selected_Row, 5).Value

I would appreciate every helpful comment

Update: I commented out every autofill function and it also outputs the right line in the table. But it won't work with autofill, here is another screenshot: here

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Datenbank")
Dim Selected_Row As Integer
'On Error Resume Next

Worksheets("Datenbank").Cells(3, 1).Value = Me.ComboBox1.Value

Selected_Row = Application.WorksheetFunction.IfError(Application.WorksheetFunction.Match(Me.ComboBox1.Value, sh.Range("E:E"), 0), Application.WorksheetFunction.Match(Application.WorksheetFunction.ValueToText(Me.ComboBox1.Value), sh.Range("E:E"), 0))

MsgBox (Selected_Row)
Me.ComboBox2.Value = Worksheets("Datenbank").Cells(Selected_Row, 6).Value
Me.ComboBox3.Value = Worksheets("Datenbank").Cells(Selected_Row, 8).Value
Me.ComboBox4.Value = Worksheets("Datenbank").Cells(Selected_Row, 10).Value
Me.ComboBox5.Value = Worksheets("Datenbank").Cells(Selected_Row, 12).Value
Me.ComboBox6.Value = Worksheets("Datenbank").Cells(Selected_Row, 14).Value
Me.ComboBox7.Value = Worksheets("Datenbank").Cells(Selected_Row, 16).Value
Me.ComboBox8.Value = Worksheets("Datenbank").Cells(Selected_Row, 18).Value
Me.TextBox15.Value = Worksheets("Datenbank").Cells(Selected_Row, 7).Value
Me.TextBox16.Value = Worksheets("Datenbank").Cells(Selected_Row, 9).Value
Me.TextBox17.Value = Worksheets("Datenbank").Cells(Selected_Row, 11).Value
Me.TextBox18.Value = Worksheets("Datenbank").Cells(Selected_Row, 13).Value
Me.TextBox19.Value = Worksheets("Datenbank").Cells(Selected_Row, 15).Value
Me.TextBox20.Value = Worksheets("Datenbank").Cells(Selected_Row, 17).Value
Me.TextBox21.Value = Worksheets("Datenbank").Cells(Selected_Row, 19).Value
Me.TextBox22.Value = Worksheets("Datenbank").Cells(Selected_Row, 5).Value
'Me.ComboBox9.Value = Worksheets("Datenbank").Cells(Selected_Row, 5).Value

excel vba match

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source

'Excel VBA Match-Lookup with numbers

Sources

Related Questions