Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with a UDF Function using Arrays...
Hello,
I am trying to write my first UDF... Basically, it takes the form of this Formula: {index($a$1:$g$100,match(c2,$b$1:$b$100,0),30)} The arrays are normally on a different sheet within the workbook. The function I created is as follows: Function McreProfile(a As Range, b As Range, c As Range, d As Integer) 'This will return the Medicare Profile on the excel sheet... McreProfile = Application.Evaluate("=Index(" & a.Address & ",match(" & b.Address & "," & c.Address & ",0)," & d & ")") End Function On the worksheet I entered and I get a #N/A =McreProfile('pprrvu03 MIA'!$A$1:$AJ$13391,Dade!I2,'pprrvu03 MIA'!$A$1:$A$13391,32) Your help is greatly appreciated... Thanks - Dennis G. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with a UDF Function using Arrays...
you need to use address(0,0,xlA1,true)
since your ranges are on other sheets. -- Regards, Tom Ogilvy "Dennis G." wrote in message ... Hello, I am trying to write my first UDF... Basically, it takes the form of this Formula: {index($a$1:$g$100,match(c2,$b$1:$b$100,0),30)} The arrays are normally on a different sheet within the workbook. The function I created is as follows: Function McreProfile(a As Range, b As Range, c As Range, d As Integer) 'This will return the Medicare Profile on the excel sheet... McreProfile = Application.Evaluate("=Index(" & a.Address & ",match(" & b.Address & "," & c.Address & ",0)," & d & ")") End Function On the worksheet I entered and I get a #N/A =McreProfile('pprrvu03 MIA'!$A$1:$AJ$13391,Dade!I2,'pprrvu03 MIA'!$A$1:$A$13391,32) Your help is greatly appreciated... Thanks - Dennis G. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with a UDF Function using Arrays...
Thanks... This works like a charm.
"Tom Ogilvy" wrote: you need to use address(0,0,xlA1,true) since your ranges are on other sheets. -- Regards, Tom Ogilvy "Dennis G." wrote in message ... Hello, I am trying to write my first UDF... Basically, it takes the form of this Formula: {index($a$1:$g$100,match(c2,$b$1:$b$100,0),30)} The arrays are normally on a different sheet within the workbook. The function I created is as follows: Function McreProfile(a As Range, b As Range, c As Range, d As Integer) 'This will return the Medicare Profile on the excel sheet... McreProfile = Application.Evaluate("=Index(" & a.Address & ",match(" & b.Address & "," & c.Address & ",0)," & d & ")") End Function On the worksheet I entered and I get a #N/A =McreProfile('pprrvu03 MIA'!$A$1:$AJ$13391,Dade!I2,'pprrvu03 MIA'!$A$1:$A$13391,32) Your help is greatly appreciated... Thanks - Dennis G. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combining arrays for a Lookup function | Excel Worksheet Functions | |||
MATCH Function - 2 Dimension Arrays | Excel Worksheet Functions | |||
Using arrays or range in IF function - HELP PLS | Excel Worksheet Functions | |||
VBA: How to pass arrays in Function Calls? | Excel Programming | |||
Multiple arrays in Median function -- VBA | Excel Programming |