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

转载于:https://www.cnblogs.com/Elsie/p/4334920.html