excel - 添加到数组并查找数组中的值

Question

我有 2 个数组:一个数组包含要在文档中搜索的值 (arr)，另一个数组将放入与找到的值关联的单元格地址 (arr2) 。我对 arr 没有任何问题，并且之前已在我的代码中成功使用过它。

使用arr2，我想查找包含arr中的值的任何单元格，并将单元格地址添加到lRow行数从它到 arr2，但前提是该地址尚不存在于 arr2 中。

我找到了 2 个答案，我试图将它们结合起来以解决我的问题，但到目前为止还没有运气。

Excel VBA - adding an element to the end of an array

How to search for string in an array

我的代码如下:

Sub Initiate()

Dim arr(3) As Variant
    arr(0) = "Value1"
    arr(1) = "Value2"
    arr(2) = "Value3"
    arr(3) = "Value4"
Dim arr2() As Variant
Dim Alc as String
Dim lRow as Long
Dim fVal as String

lRow = Activesheet.Cells(Activesheet.Rows.Count, 1).End(xlUp).Row

For Each element In arr

fVal = element

Set fRange = WA.Cells.Find(What:=fVal, LookIn:=xlFormulas _
    , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)

While Not fRange Is Nothing

    While Not IsInArray(fRange.Offset(lRow - 6, 0).Address(False, False), arr2)

        ReDim Preserve arr2(0 To UBound(arr2) + 1) As Variant

        arr2(UBound(arr2)) = fRange.Offset(lRow - 6, 0).Address(False, False)

    Set fRange = WA.Cells.Find(What:=fVal, LookIn:=xlFormulas _
    , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)

    Wend

Wend

Next element

Alc = "="

    For Each element In arr2

        Alc = Alc & element & "+"

    Next element

Alc = Left(Alc, Len(Alc) - 1)

MsgBox Alc

End Sub

Function IsInArray(stringToBeFound As String, arr2 As Variant) As Boolean

    IsInArray = (UBound(Filter(arr2, stringToBeFound)) > -1)

End Function

运行时出现以下错误:

在这行代码上(在 IsInArray 函数内):

IsInArray = (UBound(Filter(arr2, stringToBeFound)) > -1)

非常感谢任何帮助!

Answer 1

我不喜欢使用过滤器，因为它也会匹配子字符串，而且通常这不是您想要的

Function IsInArray(stringToBeFound As String, arr2 As Variant) As Boolean

    IsInArray = Not IsError(Application.Match(stringToBeFound, arr2, 0))

End Function

另外:

ReDim Preserve arr2(0 To UBound(arr2) + 1) As Variant

可能应该是:

ReDim Preserve arr2(0 To UBound(arr2) + 1)