Range VBA: double entry table

[Mnilo]

Good evening:

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

The imputs for this function are:

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 = RowUp, after this line RowUp=
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(TableName)

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

 

[Greg Wilson]

This worked for me even though the table (named range) was in a different
worksheet. The named range ("myTable") was defined as:
=Sheet3!$C$7:$G$11

Public Function LookTable(TableName As String, ColVal As String, _
RowVal As String) As String
Dim i As Long, rw As Long, col As Long
With Range(TableName)
For i = 1 To .Columns.Count
If .Cells(1, i).Value = RowVal Then
col = i
Exit For
End If
Next
For i = 1 To .Rows.Count
If .Cells(i, 1).Value = ColVal Then
rw = i
Exit For
End If
Next
End With
LookTable = Application.Evaluate("=Index(" & TableName & "," & _
rw & "," & col & ")")
End Function

Greg

 

[Mnilo]

Fully useful

Thank you very much Greg.
Best regards.