Results 1 to 9 of 9

20130312, 20:09 #1
 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 straightforward 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.

20130313, 00:03 #2
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,792
 Thanks
 139
 Thanked 705 Times in 639 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 (20072010) 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
Code:Worksheets("SHEET2").Cells(y,i).formula=jesrent 'WILL EVALUATE THE FORMULA AND PALCE RESULT IN CELL
MaudLast edited by Maudibe; 20130316 at 12:46.

20130313, 03:43 #3
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,188
 Thanks
 152
 Thanked 591 Times in 561 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

20130313, 14:49 #4
 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

20130317, 22:01 #5
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,792
 Thanks
 139
 Thanked 705 Times in 639 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
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; 20130317 at 22:16.

20130318, 08:45 #6
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,781
 Thanks
 403
 Thanked 1,554 Times in 1,407 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.
HTHMay the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

20130318, 14:23 #7
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,792
 Thanks
 139
 Thanked 705 Times in 639 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.

20130318, 14:58 #8
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,188
 Thanks
 152
 Thanked 591 Times in 561 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

20130722, 14:37 #9
 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