Results 1 to 2 of 2
  1. #1
    Lounger
    Join Date
    Jan 2002
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Reference In User Defined Function (XP)

    I have a workbook with three tabs of data. Each sheet has an identical structure, but for a different region (i.e. cell B4 is the same information in region 1 as for regions 2 and 3. I'm trying to write a user defined function that identifies whether each region has a value for that cell and if so, weights each region and sums the total. The one input for the UDF is the cell. Here's a simplified version to identify the contents of a cell on sheet 1 (the function will be on sheet 4), but it doesn't work. Any thoughts on what I'm doing wrong?

    Thanks.

    Function Test(location As Range) As Double
    Test = ActiveWorkbook.Sheets("Sheet1").Range(location).Va lue
    End Function

    In the worksheet, I entered '=Test(B4)'

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Reference In User Defined Function (XP)

    Hi,
    You need the address property of the range:
    <pre>Function Test(location As Range) As Double
    Test = ActiveWorkbook.Sheets("Sheet1").Range(location.Add ress).Value
    End Func
    </pre>


    HTH.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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