VBA：获取 ComboBox 以显示唯一值以从筛选工作表数据中进行选择 - 然后通过筛选的数据运行特定搜索

我现在能够执行以下操作，并且至少能够让 ComboBox 搜索我要过滤的列中的值。但是，一旦我开始输入第一个数字，它只会向我显示框中的值：

下拉列表有效，但是...

但是我正在尝试在下拉字段中填充每个值一次（尽管由于数据的性质，列本身将其列在多个单元格中）。一旦我选择了货件编号，我就需要数据来过滤工作表上的所有信息，以及“L”列中该特定货号的所有其他相应值（隐藏不属于该货件的所有内容，而是属于另一个货件）。

Private Sub ComboBox1_Change()

    FilterCbo1 (UCase(ComboBox1.Value))
    ComboBox1.DropDown
    
End Sub

Public Sub FilterCbo1(strFilter As String)

Dim lastrow As Long
    lastrow = ThisWorkbook.Sheets("Tabelle1").Range("L" & Rows.Count).End(xlUp).Row
        For Each strchoice In Range(Cells(2, 12), Cells(lastrow, 12))
            If InStr(1, strchoice, strFilter) <> 0 Then
                SucheTeilenummer.ComboBox1.AddItem strchoice
            End If
        Next
End Sub

以下是使用L列的唯一值填充组合框的方法：

Private Sub ComboBox1_Change()
    Dim lRow As Long
    ShowAll
    lRow = Range("L" & Rows.Count).End(xlUp).Row
    ThisWorkbook.Sheets("Tabelle1").Range("A1:AA" & lRow).AutoFilter Field:=12, Criteria1:=ComboBox1.Value, Operator:=xlFilterValues
    Me.userinput.SetFocus 'sets focus to your textbox
    Dim arr, rng As Range
    Set rng = ThisWorkbook.Sheets("Tabelle1").Range("A1:AA" & lRow).Offset(1)
    arr = rng.SpecialCells(xlCellTypeVisible).Value 'could use something like this instead of looping through your cells
End Sub

Private Sub UserForm_Initialize()
    Dim lRow As Long, i As Long, arr
    ShowAll
    lRow = Range("L" & Rows.Count).End(xlUp).Row
    arr = Range("L2:L" & lRow).Value 'all L-values
    With CreateObject("Scripting.Dictionary")
        For i = 1 To UBound(arr, 1)
            If Not .Exists(arr(i, 1)) Then
                .Add arr(i, 1), Nothing 'keeps only the unique shipping numbers
            End If
        Next i
        ComboBox1.List = .Keys 'gives the combobox those unique numbers all at once
    End With
    limiter_a = "^#01^"
    limiter_b = "^#02^"
End Sub

Private Sub ShowAll()
    On Error Resume Next
    With ThisWorkbook.Worksheets("Tabelle1")
        If .AutoFilterMode Then .ShowAllData
    End With
    On Error GoTo 0
End Sub

以下是修剪不必要的代码时其他代码的样子：

Private Sub suchbutton_Click()

    Dim fullstring As String, searchstring As String
    Dim partfound As String, locationfound As String, partqtyinfound As String
    Dim partnotein As String, partspecialin As String, lastbin As String
    Dim columnpart As String, columnlocation As String, lineqtydetail As String
    Dim quickbin2 As String
    Dim ergebnis As String
    Dim j As Long
    Dim lenght_a As Long, lenght_b As Long
    'Common mistake to not declare your variables for each, i.e. fullString in your code was a Variant, not a string variable
    Set ws = ThisWorkbook.Sheets("Tabelle1")
    
    columnpart = "M"
    columnlocation = "C"
    quickbin2 = "D"
    partqtyinfound = "N"
    partnotein = "P"
    partspecialin = "Q"
    lastbin = "B"
    lineqtydetail = "O"
    
    lenght_a = Len(limiter_a)
    lenght_b = Len(limiter_b)
    
    fullstring = SucheTeilenummer.userinput.Value
    
    openPos = InStr(fullstring, limiter_a)
    closePos = InStr(fullstring, limiter_b)
    
    If openPos > "0" And closePos > "0" Then
        searchstring = Mid(fullstring, openPos + lenght_a, closePos - openPos - lenght_b)
        letzteZeileTeilenummer = ws.Cells(ws.Rows.Count, columnpart).End(xlUp).Row
        For j = letzteZeileTeilenummer To 2 Step -1
            If Range(columnpart & j).Value = searchstring Then
                booFound = True
                
                SucheTeilenummer.partfound = ws.Range(columnpart & j).Value
                SucheTeilenummer.locationfound = ws.Range(columnlocation & j).Value
                SucheTeilenummer.partqtyinfound = ws.Range(partqtyinfound & j).Value
                SucheTeilenummer.partnotein = ws.Range(partnotein & j).Value
                SucheTeilenummer.partspecialin = ws.Range(partspecialin & j).Value
                SucheTeilenummer.lastbin = ws.Range(lastbin & j).Value
                SucheTeilenummer.lineqtydetail = ws.Range(lineqtydetail & j).Value & Chr(vbKeySpace) & "PCS" & " - " & ws.Range(columnpart & j).Value & vbCrLf & ws.Range(partnotein & j).Value & vbCrLf & "##################" & vbCrLf & SucheTeilenummer.lineqtydetail
                SucheTeilenummer.quickbin2 = ws.Range(quickbin2 & j).Value
                Exit For 'no need to keep searching
            End If
        Next j
    Else
        searchstring = "Keine Limiter gefunden"
    End If
        
    If Not booFound Then
        SucheTeilenummer.partfound = searchstring
        emptySucheForm
    End If

    SucheTeilenummer.userinput.Value = ""
    fullstring = ""
    ergebnis = ""
    SucheTeilenummer.userinput.SetFocus

End Sub

Private Sub emptySucheForm()
    SucheTeilenummer.locationfound = ""
    SucheTeilenummer.partqtyinfound = ""
    SucheTeilenummer.partnotein = ""
    SucheTeilenummer.partspecialin = ""
    SucheTeilenummer.lastbin = ""
    SucheTeilenummer.lineqtydetail = ""
    SucheTeilenummer.quickbin2 = ""
End Sub

Private Sub userinput_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

If KeyCode = vbKeyReturn Then 'wenn die Taste vbKeyReturn ist was für 13 steht dann soll die Suche gestartet werden
    Call suchbutton_Click
    KeyCode = 0 'hier wird der Key für die nächste Suche resettet
End If

End Sub

Private Sub userinput_change()
If InStr(SucheTeilenummer.userinput.Value, limiter_b) Then 'wenn in dem Einfgabefeld irgendwo der limiter_b auftaucht, dann soll die Suche gestartet werden
    Call suchbutton_Click
End If

End Sub

如果我更多地了解 M 列项目编号的唯一性，这可以得到改进。我保留了你的大部分代码，因为如果它们不是唯一的，重点是得到最后一个，我不想打破这个逻辑（现在）。如果是，则会想到使用 的 来按顺序获取剩余值。Application.Match