Reputation: 434
I am trying to find a value in a named table (i.e. a listobject) but failing miserably. I have a page containing a table of the same name as the page. So far I have 3 working lines to find the word "Area" in the description column and return the row number. This is:
Function CheckArea(ByVal My_Table As String)
Set Table = Worksheets(My_Table).ListObjects(My_Table)
Descriptions = Table.ListColumns("Description").Range
found_cell = Application.Match("Area", Descriptions, 0)
Once I have found the row, I want to access the information in the same row but a different column "Schem typ" and return the value contained. I cannot find how to access a cell within the table by a named column and a referenced row. If should be simple but I cannot find any examples out there. Can anyone help?
Upvotes: 0
Views: 3106
Reputation: 12008
Try this
Dim MyTable As ListObject
Set MyTable = ThisWorkbook.ActiveSheet.ListObjects("Tabla_1")
Dim ZZ As Long
Dim MySearch As String
MySearch = "caca" 'change this whatever you want
On Error GoTo Errores:
ZZ = Application.WorksheetFunction.Match(MySearch, MyTable.ListColumns(1).Range, 0) - 1 'minus 1 to exclude header. Change 1 for number of column where you are searching
Debug.Print MyTable.DataBodyRange.Cells(ZZ, 2).Value 'Change 2 for number of column you want to obtain
Clear_All:
Set MyTable = Nothing
Exit Sub
Errores:
If Err.Number = 1004 Then
MsgBox MySearch & " not found"
GoTo Clear_All
Else
MsgBox Err.Description
End If
The code will use Match Function to find the value. IF not found, it will pop up a message (not the best error handling I must admit).
Try to adapt it to your needs.
Upvotes: 0