Excel VBA -计数副本

Question

我有两个数组，其中包含字符串值。让我们考虑下面的例子。

• 阵列1:计算机科学、人工智能
• 数组2: Eclipse，MS，RAD，Linux，人工智能

我需要在我的两个数组中找到重复的数量。在上述情况下，我需要总复制值为1(因为人工智能是在Array1和Array2中)。在VBA中有办法做到这一点吗？

Answer 1

这个函数假设arr2是一个一维数组：

Function ArrDupCount(ByVal arr1 As Variant, ByVal arr2 As Variant) As Long

    Dim varElement As Variant
    Dim lMatch As Long

    On Error Resume Next
    For Each varElement In arr1
        lMatch = 0
        lMatch = WorksheetFunction.Match(varElement, arr2, 0)
        If lMatch > 0 Then ArrDupCount = ArrDupCount + 1
    Next varElement
    On Error GoTo 0

End Function

使用它：

Sub tgr()

    Dim arr1 As Variant
    Dim arr2 As Variant

    arr1 = Array("Computer science", "Artificial Intelligence")
    arr2 = Array("Eclipse", "MS", "RAD", "Linux", "Artificial Intelligence")

    MsgBox ArrDupCount(arr1, arr2)  ' => 1

End Sub