Excel Table Functions: a Find returns the sheet row rather than the table row

[rtemen]

I have a small program that is trying to find a cell containing a certain string and return the Row in the table where it is found.

Here is my program:

Private Sub test()
    'Routine to move data to table from Molding Table
    'Check to not overwrite current records but add new ones.
    
    Dim summObj As ListObject
    Dim I, X As Integer
    Dim foundrow As Integer
    
    ' Get the table reference
    Set summObj = Worksheets("Summary").ListObjects("SummaryTable")
    With summObj
    
    foundrow = .ListColumns("ID").Range.Find("1,3").Row
    foundrow = .ListColumns("ID").DataBodyRange.Find("1,3").Row
    End With


End Sub

Two questions:
1. Both of my commands to find the row return the spreadsheet row and not the table row.
2. If the string is not in the table row, it raises the RunTime Error '91'. What is wrong?

Rich

 

[Jeffrey Mahoney]

You are going to have to subtract the header row of the table from the foundrow.

When you search for a non-existent string it will return an error, you need to mitigate that.

Dim foundrow as variant
foundrow = .ListColumns("ID").Range.Find("1,3").Row
if Iserror(foundrow) = true then foundrow = 0

 

[Rick Rothstein]

As Jeffrey pointed out, you have to do a subtraction to get the row number within the table and you also need to provide an error trap should you accidentally search for an ID that does not exist. This is how I would write such a macro...

[table="width: 500"]
[tr]
	[td]Private Sub test()

  Dim summObj As ListObject
  Dim I As Long, X As Long
  Dim foundrow As Long

  Set summObj = Worksheets("Summary").ListObjects("SummaryTable")
  On Error Resume Next
  foundrow = summObj.ListColumns("ID").Range.Find("4,5").Row - summObj.DataBodyRange.Row + 1
  On Error GoTo 0

End Sub[/td]
[/tr]
[/table]

Note1: If you search for text that is not in the table, foundrow will be assigned a value of 0.

Note2: I changed your Integer declarations to Long data types. There is nothing to be gained by using Integer and you avoid potential problems when you use Long instead of Integer.

 

[NoSparks]

Could you not use the Match function to give the ListRow directly ?

    On Error Resume Next
    foundrow = Application.WorksheetFunction.Match("1,3", .ListColumns("ID").DataBodyRange, 0)
        If foundrow = 0 Then
            MsgBox " Nothing found"
        Else
            MsgBox "foundrow = .ListRows(" & foundrow & ")"
        End If
    On Error GoTo 0

 

[Rick Rothstein]

Could you not use the Match function to give the ListRow directly ?

Great idea!

 

[rtemen]

The Match is doing the trick.
Thanks much.
Rich

 

[rtemen]

Hi there.
I do not know how or where to ask this question, so I will try to ask it here. (Or, please tell me where to ask this question)
When working with a ListObject, both the Microsoft info as well as the interactive syntax checker shows that there is a Find for the ListObject.
So, while you can select cells in a table using the TABLE rows and columns, update cells using the TABLE rows and columns, etc, the Find gives the spreadsheet row and column rather than the TABLE's????
What kind of logic is this?
So, first, am I missing some sort of high level assignment or setting or something that will make the FIND answer in the Table's lingo?
If the FIND that is attached to the various ListObject objects does not work, then why is it there???
Just frustrated with the hours of Googling to find help and nothing helps or describes why it doesn't work and how you are supposed to handle it.
Thanks,
Rich

 

[NoSparks]

Apparrently .Find always gives the sheet address
I found this type of work around, maybe it's adaptable

    Set oLo = Sheet1.ListObjects(1)
    
    If Not Intersect(oLo.DataBodyRange, ActiveCell) Is Nothing Then
        MsgBox "oLo column    " & Range(oLo.DataBodyRange(1), ActiveCell).Columns.Count & vbLf & _
               "oLo row           " & Range(oLo.DataBodyRange(1), ActiveCell).Rows.Count
    End If