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

Question

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

BigBen · Accepted Answer · 2020-10-07 02:10:11Z

5

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

answered Oct 7, 2020 at 2:10

BigBen

50.4k7 gold badges29 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

ACCtionMan Over a year ago

Thanks BigBen, that's much more efficient. I just tested it and worked a treat. In regards to explicitly setting getRowNum = 0, I'm guessing because getRowNum has been declared a Long, it will be initialized as 0 anyway, hence the shorter code?

Owennn Over a year ago

Hi @Bigben , just out of curiosity, what if there are more than one match in the table? How do you recommend to get all row positions for each of the match? Your reply will mean a lot to me. Thanks!

BigBen Over a year ago

@Owennn perhaps read the data into a Variant array and use a loop.

Owennn Over a year ago

@BigBen, - thanks for the idea! will it work with your function or does it has to be a loop? it seems Application.Match only gives the first match in the table.

BigBen Over a year ago

@Owennn yes Application.Match only gives the first match so you'd just use a loop; however, looping an array should be very fast.

Collectives™ on Stack Overflow

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

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related