hallpett
asked on
Rewrite a table lookup function in VBA
Found the GetTableEntry function below on this site:
It works but I would like to rewrite it so I could use an actual named Excel table as the LookupTable parameter instead of a range.
It workes with this formula: =gettableentry(A1:C3,"Ben" ,"Orange")
I would like to rewrite to work with this: =gettableentry("tblFruits" ,"Ben","Or ange")
It works but I would like to rewrite it so I could use an actual named Excel table as the LookupTable parameter instead of a range.
It workes with this formula: =gettableentry(A1:C3,"Ben"
I would like to rewrite to work with this: =gettableentry("tblFruits"
Public Function GetTableEntry( _
ByVal LookupTable As Range, _
ByVal RowHeader As String, _
ByVal ColHeader As String)
With Application
.Volatile
With .WorksheetFunction
GetTableEntry = LookupTable.Parent.Cells( _
.Match(RowHeader, LookupTable.Columns(1), 0) + LookupTable.row - 1, _
.Match(ColHeader, LookupTable.Rows(1), 0) + LookupTable.Column - 1).Value
End With
End With
End Function
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You're welcome
how is mine assisted, when it is the same solution as the other, and given first?
ASKER
Sorry. I just share the points between you since you gave med the idea to do some more testing and MacroShadow presicely told me that there was no need for rewriting. Probably should have given you the "Accepted solution", but was not aware that this ment something.
ASKER
=gettableentry("tblFruits"
to:
=gettableentry(tblFruits[#
( the [#Alle] is Norwegian Excel, guess it should be [#All] in English Version)
Thanks!