3

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
ACCtionMan
  • 511
  • 1
  • 3
  • 12

1 Answers1

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
BigBen
  • 46,229
  • 7
  • 24
  • 40
  • 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? – ACCtionMan Oct 07 '20 at 06:08
  • 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! – Owennn May 12 '21 at 08:58
  • 1
    @Owennn perhaps read the data into a `Variant` array and use a loop. – BigBen May 12 '21 at 09:29
  • @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. – Owennn May 12 '21 at 13:45
  • 1
    @Owennn yes `Application.Match` only gives the first match so you'd just use a loop; however, looping an array should be very fast. – BigBen May 12 '21 at 13:48