CODEWITHSUNDEEP
excelvbalistobject
Stephen Ellwood
Stephen Ellwood

Reputation: 434

Trying to access named tables with VBA to lookup a value

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

Answers (2)

jkpieterse
jkpieterse

Reputation: 3006

Table.ListColumns("Schem Typ").Range.Cells(found_Cell)

Upvotes: 1

Foxfire And Burns And Burns
Foxfire And Burns And Burns

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

Related Questions