Using parts of a defined range for UDF

  • Thread starter Thread starter Hoss
  • Start date Start date
H

Hoss

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
 
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
 
Thanks Tim this worked perfectly.

Tim Zych said:
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
Click to expand...
 
Back
Top