Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Sep 2011
    Posts
    25
    Thanks
    8
    Thanked 0 Times in 0 Posts

    VB Code required to evaluate Excel formula

    Hi,

    I'm hoping this can be solved. I have a pretty straight-forward macro that uses a
    NAME process (SUMIF formula to update a range of cells in one sheet from imported
    data in another sheet), with this line of code.

    Cells(y, i).Value = "=jesrent"

    My problem is that this places the "=jesrent" formula into each cell, whereas I need
    it to evaluate the formula & place the resulting $$cc amount in the cell instead
    - as a Copy/Paste Special/Value step would. Note that the correct amount is in fact
    ending up in each cell, but there are specific reasons for this to be in a 'value' form
    & not a formula.

    A copy of my code is attached. This works exactly as required, except for the issue
    outline above.

    Any comments or suggestions would be greatly appreciated.
    Attached Files Attached Files

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Not sure if this is what you are looking for.

    Assuming jesrent is set as a name, jesrent must then be set to a cell formula by selecting the cell then clicking "Use in formula" on the formulas menu (2007-2010) OR it can be done through code. For sheet1, place in a standard module:

    Code:
    Public jesrent as Variant
    
    Public Sub name()
       '----code---------
       jesrent = Worksheets("SHEET1").Range("G1").Formula  'CELL CONTAINING SUMIF FORMULA
       '----code---------
    end sub
    jesrent can then be used to set the formula of Cells(y,i) on another sheet and return a value using the following line (nested in your loops):
    Code:
    Worksheets("SHEET2").Cells(y,i).formula=jesrent  'WILL EVALUATE THE FORMULA AND PALCE RESULT IN CELL
    HTH,
    Maud
    Last edited by Maudibe; 2013-03-16 at 13:46.

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi John

    Welcome to the lounge!

    Try this:

    Cells(y, i)= "=jesrent"
    Cells(y,i) = Cells(y,i)

    ..the first vba line places the formula in the cell, and computes the value as before.
    ..the second vba line will then convert it to a value.

    zeddy

  4. #4
    Lounger
    Join Date
    Sep 2011
    Posts
    25
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Maude & Zeddy,

    Many thanks for your prompt & informative responses - much relief on my part let me assure you.

    I will check both approaches & let you know the results.

    Maude. I'm using Excel 2003/SP3 at present, so I guess I'll have to use your 'do through Code' method.

    Again, many thanks to you both

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    John,
    After reading a little deeper into what you want to do, please ignore my previous post. I had mistaken that the text "=jesrent" was being placed in each cell. Apologies.

    The solution that Zeddy offered will convert the formula to a value as it is being copied over. As an alternative, the following code will convert the formulas to values after all the formulas have been copied to the new sheet (after loop completed).

    For a specific range:
    Code:
    Sub Formula2Value()
        Dim Rng As Range
        Set Rng = Range("A1:D4")  'CHANGE RANGE TO MEET NEEDS
        Rng.Select
        Selection.Copy
        Rng.Select
        Selection.PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False
    End Sub
    For the entire sheet:
    Code:
    Sub Formula2Value()
        Cells.Select
        Selection.Copy
        Cells.Select
        Selection.PasteSpecial Paste:=xlPasteValues
        Range("A1").Select
        Application.CutCopyMode = False
    End Sub
    Last edited by Maudibe; 2013-03-17 at 23:16.

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Hey Y'all,

    Unless I'm missing something why not just replace
    Cells(y, i).Value = "=jesrent"
    with
    Cells(y, i).Formula = "=jesrent"

    It works just fine on my Excel 2010.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    RG,

    I believe he doesn't want a formula in the cell giving the evaluated value. He just wants the value with no formula.

  8. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi RG

    perhaps you are missing something...

    As per original post:

    "..I need it to evaluate the formula & place the resulting $$cc amount in the cell instead
    - as a Copy/Paste Special/Value step would"

    "Note that the correct amount is in fact
    ending up in each cell, but there are specific reasons for this to be in a 'value' form
    & not a formula."

    so, my solution should actually read:
    Cells(y, i)= "=jesrent"
    Cells(y,i) = Cells(y,i).value

    ..the first vba line places the formula in the cell, computing the value as before.
    ..the second vba line will then converts the formula to a value only

    zeddy

  9. #9
    Lounger
    Join Date
    Sep 2011
    Posts
    25
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Zeddy, Maude & RG. A much belated thank you for your replies to this Post. I've been on the move a lot since March & have only found the time to pursue this again. It worked fine with Zeddy's 2 line 'Cells' approach, so I quit while I was ahead. Again, many thanks.

    John

Posting Permissions

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