Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
breaking down a whole number into pre-defined parts.. | Excel Discussion (Misc queries) | |||
Application-defined or object-defined error (worksheet, range, sel | Excel Programming | |||
Copying A Range of Cells From one Sheet to Another - Error Application Defined or Object Defined | Excel Programming | |||
Range - Application Defined or Object Defined Error | Excel Programming | |||
Row parts and range confusion | Excel Programming |