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

    Returning Value/Text (XL97; SR2)

    Is there a more streamlined approach to return a cell's value or text where a variable is equal to a formula?

    Example:
    sub start()
    Status = "=+'Your formula here" 'Status is a formula
    ActiveCell.offset(0,1).value = Status
    End Sub

    I still receive the formula in the cell.offset. I should be returned the cell contents of "Status"

    Thanks,
    John

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

    Re: Returning Value/Text (XL97; SR2)

    I'm not exactly sure what the problem is, but gather that you get the formula displayed rather that the computed value. This may be because when the formula is entered into the cell, the cell has Text formatting. Try applying the General format to the cells and run the code again.

    Andrew C

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

    Re: Returning Value/Text (XL97; SR2)

    Andrew,

    Changing the cell format did not work.

    I tweaked the code to include:
    ActiveCell.Offset(0, 1).Value = ActiveCell.Offset(0, 1)

    This works but the code previously mentioned should have worked as well.

    Thanks,
    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: Returning Value/Text (XL97; SR2)

    John,

    Did you re-run the code after applying the formatting ?.

    One or 2 technical points.

    ActiveCell.offset(0,1).value = Status might be more correctly written as
    ActiveCell.offset(0,1).Formula= Status,

    though I do not think it would matter it the cell formatting is text.

    Also the + in "=+'Your formula etc, is really redundant in Excel.

    Andrew C

Posting Permissions

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