VBA實現隨意輸入組合碼，查詢唯一標識碼

標籤：

記錄背景：

需要在excel中查詢出組合碼，對應的唯一標識碼。

舉例 組合碼：4+5+6+9+1*2   標識碼：A1

介面隨意輸入組合碼：1*2+4+5+6+9  輸出標識碼：A1

VBA實現：

Private Sub CommandButton1_Click()
    TextBox2.Value = ""
    Dim str, searchValue
    Dim i
    i = 1
    searchValue = TextBox1.Value
   
    Dim arrSearchValueList
    arrSearchValueList = Split(searchValue, "+")
    ‘MsgBox UBound(arrSearchValueList) - LBound(arrSearchValueList)
    Dim searDict
    Set searDict = CreateObject("Scripting.Dictionary")
    Dim k&
    For k = 0 To UBound(arrSearchValueList)
         searDict(arrSearchValueList(k)) = ""
    Next
   
    Dim txtResult
    txtResult = TextBox2.Value
   
    For R = 1 To Worksheets(1).UsedRange.Rows.Count
            str = Worksheets(1).Cells(R, 1).Value
            Dim arrSourceValueList
            arrSourceValueList = Split(str, "+")
           
            If (UBound(arrSearchValueList) - LBound(arrSearchValueList)) = (UBound(arrSourceValueList) - LBound(arrSourceValueList)) Then
                Dim j&, a&
                a = 0
                For j = 0 To UBound(arrSourceValueList)
                    If searDict.exists(arrSourceValueList(j)) Then
                        a = a + 1
                    Else
                        a = a - 1
                    End If
                Next
                If ((a - 1) = (UBound(arrSearchValueList) - LBound(arrSearchValueList))) Then
                    If txtResult = "" Then
                        txtResult = str
                    Else
                        txtResult = txtResult & "|" & str
                    End If
                End If
                Set d = Nothing
            End If
        i = i + 1
    Next
   
    TextBox2.Value = txtResult
End Sub

VBA實現隨意輸入組合碼，查詢唯一標識碼