Excel VBA - 在表列中查找给定值的行号

Question

背景

我不久前编写了这段代码，它可以在一个电子表格中运行。它实际上被调用以在第一个表列中查找给定值的表行号。然后，其余代码使用此表行号来更新该行的值。

我最近将相同的方法应用于另一个电子表格，直到昨天它仍然有效。现在在 myArray = tbl.DataBodyRange 行上，我收到运行时错误“6”(溢出)。最近的电子表格中的表有更多的数据，所以 myArray 不能再保存表数据。

我修改了我的代码以使用 ListRows 搜索表格行，然后检查第一列的每个值，直到找到我要查找的内容。

在这两个例程中，如果未找到该值，则返回 0 并且其他代码知道不要尝试更新表行。

问题

我是否可能会在修改后的方法中遇到更多问题和/或是否有更有效的方法来查找我正在寻找的行号。该表目前有大约 700 行数据，并将在接下来的几个月内增加到 4,000 多行。

代码溢出错误

Function getRowNum(ByVal valueToFind As String)
    Dim tbl As ListObject
    Dim myArray As Variant
    Dim x As Long
    Dim checkvalueToFind As String
    Dim rowFound As Integer
    
    rowFound = 0
    
    Set tbl = Range("table_masterList").ListObject
    
    myArray = tbl.DataBodyRange
    
    For x = LBound(myArray) To UBound(myArray)
        checkvalueToFind = myArray(x, 1)
        'Debug.Print checkvalueToFind
        If checkvalueToFind = valueToFind Then
            rowFound = x
            GoTo foundIt
        End If
    Next x
    
foundIt:
    
    Set tbl = Nothing
    getRowNum = rowFound
    
End Function

修改后的代码

Function getRowNum2(ByVal valueToFind As String)
    Dim tbl As ListObject
    Dim row As ListRow
    Dim checkvalueToFind As String
    Dim rowFound As Integer
    
    rowFound = 0
    
    Set tbl = Range("table_masterList").ListObject
    
    For Each row In tbl.ListRows
        checkvalueToFind = tbl.DataBodyRange.Cells(row.Index, 1).Value
         If checkvalueToFind = valueToFind Then
            rowFound = row.Index
            GoTo foundIt
        End If
    Next row
        
foundIt:
    
    Set tbl = Nothing
    getRowNum2 = rowFound
    
End Function

Answer 1

循环过于复杂。只需使用匹配:

Function getRowNum(ByVal valueToFind As String) As Long
    ...
    Dim matchResult As Variant
    matchResult = Application.Match(valueToFind, tbl.ListColumns(1).DataBodyRange, 0)

    If IsError(matchResult) Then
       getRowNum = 0
    Else
       getRowNum = matchResult
    End If
End Function

或者稍微简单一点:

Function getRowNum(ByVal valueToFind As String) As Long
    ...
    Dim matchResult As Variant
    matchResult = Application.Match(valueToFind, tbl.ListColumns(1).DataBodyRange, 0)

    If Not IsError(matchResult) Then
       getRowNum = matchResult
    End If
End Function