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.

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.

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.