时间戳

首页 > 解决方案 > 在 Excel (VB) 中查找值

excel - 在 Excel (VB) 中查找值

问题描述

我正在努力让我的 vb 脚本正常工作。

我需要在数组中找到值 a(string) 和 b(date)。

例子：

[string1][date1] where in arr[[st1][dat1],[st2][dat2],[string1][date1]]

最后一个将匹配 true 第一个和第二个将是 false

价值观：

表 1： 表 1

表 2： 表 2 我的代码：

Function IsInArray(stringToBeFound1 As String, ByVal dateToBefound As Date, arr As Variant) As Boolean
   Dim Found1 As Boolean
   Dim Found2 As Boolean
   Dim x As Integer
   
   Found1 = False
   Found2 = False
   x = 0
   
    For x = 0 To UBound(arr, 1) // this does not seem to work
        If stringToBeFound1 = arr(x, 0) Then
            Found1 = True
        End If
        If dateToBefound = arr(x, 1) Then
            Found2 = True
        End If
        If Found1 = 1 And Found2 = 1 Then IsInArray = True
    Next

End Function

用法：=IsInArray(C6,D6,'Sheet2'!C:D)

标签： excelvba

解决方案

您的代码的主要错误是您提供给函数的参数不是数组。这是一个范围。请阅读下面代码中的更多评论。

Function IsInArray(stringToBeFound As String, _
                   ByVal dateToBefound As Date, _
                   Rng As Range) As Boolean
    ' =IsInArray(C6,D6,'Sheet2'!C:D)        ' C:D is a range, not an array

    Dim MyRng   As Range
    Dim Arr     As Variant
    Dim R       As Long             ' loop counter: rows
   ' since there are more than a million rows in a sheet
   ' and an integer can only count up to abt 32000
   ' you can't use an Integer to loop through all rows
   
'   Found1 = False          ' }
'   Found2 = False          ' } These values are given by the Dim statements above
'   x = 0                   ' }

    ' all the column is too big a range.
    ' it will take ages to find nothing.
    ' Exclude blank cells at the bottom:-
    With Rng
        Set MyRng = Range(.Cells(1), Cells(Rows.Count, .Column).End(xlUp)) _
                    .Resize(, .Columns.Count)
'        ' you may prefer this one, actually:
'        Set MyRng = Range(.Cells(2, 1), Cells(Rows.Count, .Column).End(xlUp)) _
'                    .Resize(, .Columns.Count)
        Debug.Print MyRng.Address       ' for testing MyRng
    End With
    Arr = MyRng.Value       ' creates a 1-based 2-D array
    For R = 1 To UBound(Arr, 1)
        ' always compare the code-generated variable with the given constant
        If Arr(R, 1) = stringToBeFound Then
'        ' you may prefer a case-insensitive comparison:-
'        If StrComp(Arr(R, 1), stringToBeFound, vbTextCompare) = 0 Then
            If dateToBefound = Arr(R, 2) Then
                IsInArray = True
                Exit For
            End If
        End If
    Next
End Function

我使用下面的代码使用您想要的语法和在 VBA 中将函数作为 UDF 进行了测试。将其用作 UDF 时，我敦促您使用绝对地址引用目标范围，例如Sheet2'!$C:$D. 不这样做更像是自找麻烦。

Private Sub Test()
    ' =IsInArray(C6,D6,'Sheet2'!C:D)
    Debug.Print IsInArray("B", Date + 1, Sheet2.Columns("C:D"))
End Sub

推荐阅读