CODEWITHSUNDEEP
excelvba
ACCtionMan
ACCtionMan

Reputation: 511

Excel VBA - Find Row Number for Given Value in a Table Column

Background

I built this code a while ago and it works in one spreadsheet. It essentially gets called to find the table row number for a given value in the 1st table column. The rest of the code then uses this table row number to update the values for that row.

I recently applied the same to another spreadsheet and it was working until yesterday. Now on the line myArray = tbl.DataBodyRange I get a Run-time error '6' (Overflow). The table in the recent spreadsheet has much more data, so myArray can no longer hold the table data.

I have revised my code to search through the table rows using ListRows and then checking each value of the 1st column until I find what I am looking for.

In both routines, if the value is not found, it returns 0 and the other code knows not to attempt to update the table row.

Question

Am I likely to come across further issues with my revised approach and/or is there a more efficient way to find the row number I'm looking for. The table currently has about 700 rows of data and will grow to over 4,000 over the next few months.

Code with Overflow Error

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

Revised Code

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

Upvotes: 3

Views: 24656

Answers (1)

BigBen
BigBen

Reputation: 49998

Looping is over-complicating. Just use Match:

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

Or slightly simpler:

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

Upvotes: 5

Related Questions