Table Manipulation – Lookup List Object Value
Searches for a specified value in the first column of an Excel.ListObject and returns the row index if found.
This function uses the Find method on the table’s data body range, looking only in the first column, with LookIn:=xlValues and LookAt:=xlWhole for exact match. If the value is not found or an error occurs, the function returns 0.
VB
Public Function xlLookupListObjectValue(ListObject As Excel.ListObject,
LookupValue As String) As Integer
If ListObject Is Nothing Then Return 0
Try
Dim FoundCell As Excel.Range = Nothing
'Attempt to find value in Table's first Column
Try : FoundCell = ListObject.DataBodyRange.Find(LookupValue, LookIn:=xlValues, LookAt:=xlWhole)
Catch ex As Exception : End Try
'Return Table Row number if value is found
If Not FoundCell Is Nothing Then
Return ListObject.ListRows(FoundCell.Row - ListObject.HeaderRowRange.Row).Index
Else
Return 0
End If
Catch ex As Exception
Return 0
End Try
End Function