Range vba: double entry table
 

Good evening:

I´m trying to create a vba function to look for in a double entry table.

The imputs for this function a

1 the table name in Excel
2 the name to look for in the left column
3 the name to look for in the up row

The main problem is I want to use the function in an worksheet and the
double entry table can be in different worksheet (usually will be in another
worksheet), and because of that I have some problems with the ranges.

You can read the code below (it is a short function) as it currently is, the
problem start in the line Set = FilaSuperior, after this line FilaSuperior =
nothing and the the code doesn´t work

Thanks in advance for your help.

Function LookTable(TableName As String, DataLeftColumn As Variant, DataRowUp
As Variant) As Variant

Dim TableRange As Range
Dim RowUp As Range
Dim LeftColumn As Range


Set TableRange = worksheets(Range(TableName).Parent.Name).Range(Tab leName)

Set RowUp = TableRange.Range(Cells(1, 1), Cells(1,
TableRange.Columns.Count))

Set LeftColumn = TableRange.Range(Cells(1, 1), Cells(TableRange.Rows.Count,
1))

LookTable = WorksheetFunction.Index(TableRange, _
WorksheetFunction.Match(DataRowUp, _
RowUp, _
0), _
WorksheetFunction.Match(DataLeftColumn, _
LeftColumn, _
0))

End Function