+ Reply to Thread
Results 1 to 3 of 3

Help with a UDF Function using Arrays...

  1. #1
    Dennis G.
    Guest

    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. #2
    Tom Ogilvy
    Guest

    Re: 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." <[email protected]> wrote in message
    news:[email protected]...
    > 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. #3
    Dennis G.
    Guest

    Re: 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." <[email protected]> wrote in message
    > news:[email protected]...
    > > 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1