Using parts of a defined range for UDF
 

Hi,
I have been banging my head against the wall trying to figure out how to use
parts of a range for an udf. I am trying to merge Index and Match into one
function where there is only 3 inputs. The point of this is to trim down the
over head. My thought was that if I could difine parts of that defined range
in VB thus not having to define 3 areas in the workbook. I am fairly new to
VB and have been learning as I go so the answer may be really obvious and I
am just not seeing it. Here is my code:
Excel 2003

Function IndexMatch(Table, VSearch, HSearch)
Dim Table1 As Object
Set Table1 = Range("Table").Offset(1, 1).Resize(Table.Rows.Count - 1,
Table.Columns.Count - 1)
Application.Volatile
Dim Table2 As Object
Set Table2 = Range("Table").Columns(2, 1)
Dim Table3 As Object
Set Table3 = Range("Table").Rows(1, 2)
IndexMatch = WorksheetFunction.Index(TableA,
WorksheetFunction.Match(VSearch, Table2, False),
WorksheetFunction.Match(HSearch, Table3, False))
End Function

Table is to be the table which includes row identifiers and headers to
reference. I am then trying to in effect split that one defined area into 3
areas where one is the header one is the Column for Row identifiers and the
other is the Data. Thanks for you help in advance, it is much appreciated.

Hoss

Using parts of a defined range for UDF
 

I don't really follow the table structure, but perhaps this adjustment will
help. It treats the first col and first row as the V and H Search columns.

Function IndexMatch(Table As Range, VSearch As Variant, HSearch As Variant)
As Variant
Application.Volatile
Dim Table2 As Range
Set Table2 = Table.Columns(1)
Dim Table3 As Range
Set Table3 = Table.Rows(1)
IndexMatch = Application.Index(Table, _
Application.Match(VSearch, Table2, 0), _
Application.Match(HSearch, Table3, 0))
End Function


--
Tim Zych
SF, CA

"Hoss" wrote in message
...
Hi,
I have been banging my head against the wall trying to figure out how to
use
parts of a range for an udf. I am trying to merge Index and Match into
one
function where there is only 3 inputs. The point of this is to trim down
the
over head. My thought was that if I could difine parts of that defined
range
in VB thus not having to define 3 areas in the workbook. I am fairly new
to
VB and have been learning as I go so the answer may be really obvious and
I
am just not seeing it. Here is my code:
Excel 2003

Function IndexMatch(Table, VSearch, HSearch)
Dim Table1 As Object
Set Table1 = Range("Table").Offset(1, 1).Resize(Table.Rows.Count - 1,
Table.Columns.Count - 1)
Application.Volatile
Dim Table2 As Object
Set Table2 = Range("Table").Columns(2, 1)
Dim Table3 As Object
Set Table3 = Range("Table").Rows(1, 2)
IndexMatch = WorksheetFunction.Index(TableA,
WorksheetFunction.Match(VSearch, Table2, False),
WorksheetFunction.Match(HSearch, Table3, False))
End Function

Table is to be the table which includes row identifiers and headers to
reference. I am then trying to in effect split that one defined area into
3
areas where one is the header one is the Column for Row identifiers and
the
other is the Data. Thanks for you help in advance, it is much
appreciated.

Hoss

Using parts of a defined range for UDF
 

Thanks Tim this worked perfectly.

"Tim Zych" wrote:

I don't really follow the table structure, but perhaps this adjustment will
help. It treats the first col and first row as the V and H Search columns.

Function IndexMatch(Table As Range, VSearch As Variant, HSearch As Variant)
As Variant
Application.Volatile
Dim Table2 As Range
Set Table2 = Table.Columns(1)
Dim Table3 As Range
Set Table3 = Table.Rows(1)
IndexMatch = Application.Index(Table, _
Application.Match(VSearch, Table2, 0), _
Application.Match(HSearch, Table3, 0))
End Function


--
Tim Zych
SF, CA

"Hoss" wrote in message
...
Hi,
I have been banging my head against the wall trying to figure out how to
use
parts of a range for an udf. I am trying to merge Index and Match into
one
function where there is only 3 inputs. The point of this is to trim down
the
over head. My thought was that if I could difine parts of that defined
range
in VB thus not having to define 3 areas in the workbook. I am fairly new
to
VB and have been learning as I go so the answer may be really obvious and
I
am just not seeing it. Here is my code:
Excel 2003

Function IndexMatch(Table, VSearch, HSearch)
Dim Table1 As Object
Set Table1 = Range("Table").Offset(1, 1).Resize(Table.Rows.Count - 1,
Table.Columns.Count - 1)
Application.Volatile
Dim Table2 As Object
Set Table2 = Range("Table").Columns(2, 1)
Dim Table3 As Object
Set Table3 = Range("Table").Rows(1, 2)
IndexMatch = WorksheetFunction.Index(TableA,
WorksheetFunction.Match(VSearch, Table2, False),
WorksheetFunction.Match(HSearch, Table3, False))
End Function

Table is to be the table which includes row identifiers and headers to
reference. I am then trying to in effect split that one defined area into
3
areas where one is the header one is the Column for Row identifiers and
the
other is the Data. Thanks for you help in advance, it is much
appreciated.

Hoss