Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA Function (XL97; SR-2)

    I'm trying to write a custom function that tests the value in one cell and if true references the value in another. I can do it for one cell using the "activecell" in the function but if I copy the formula down to other cells the value of the referred cell changes based on the location of the activecell.

    The custom function defined in the attached XL file is as follows:

    Function New1(Amount)
    If Amount = 4 Then
    New1 = ActiveCell.Offset(0, -1).Value
    End If
    End Function
    Attached Files Attached Files

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Function (XL97; SR-2)

    See if this does what you want:

    <pre>Function New1(Amount As Range)
    If Amount.Value = 4 Then
    New1 = Amount.Offset(0, 1).Value
    End If
    End Function
    </pre>

    Legare Coleman

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Function (XL97; SR-2)

    It works.

    Thank you,
    John

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: VBA Function (XL97; SR-2)

    Loking at your example, I am prompted to ask why not use something like <pre> =IF(B7=4,C7,"")</pre>

    Am I missing something ?

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Function (XL97; SR-2)

    I am trying to write a custom function in VBA code.

Posting Permissions

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