+ Reply to Thread
Results 1 to 11 of 11

Putting value instead of formula itself using VBA code

  1. 12-12-2011, 09:54 AM #1
    goels
    goels is offline
    Registered User
    Join Date
    10-31-2010
    Location
    London
    MS-Off Ver
    Excel 2007, Excel 2010
    Posts
    56

    Putting value instead of formula itself using VBA code

    Hi All,

    I have the following code which puts the formula in rows J2 to J5000. What I want to do though is place the calculation result of the formulas in rows J2 to J5000. How can I do that?

    Please Login or Register  to view this content.
    So for eg currently it places in cell J2 =IF(A2="","",IF(OR(D2=1,F2=1),999999,ABS((D2*E2)/(F2*G2)))) which calculates to say 4. I just want it to place value 4 in cell J2 without the formula.

    Thanks
    Last edited by goels; 12-16-2011 at 12:42 PM.
    Reply With QuoteRegister To Reply
  2. 12-12-2011, 10:08 AM #2
    tom1977
    tom1977 is offline
    Forum Expert
    Join Date
    09-27-2011
    Location
    Poland
    MS-Off Ver
    Excel 2007
    Posts
    1,312

    Re: Putting value instead of formula itself using VBA code

    Look at such example and try to adjust 

    Please Login or Register  to view this content.
    Regards

    tom1977

    If You are satisfied with my solution click the small star icon on the left to say thanks.
    Reply With QuoteRegister To Reply
  3. 12-12-2011, 10:55 AM #3
    goels
    goels is offline
    Registered User
    Join Date
    10-31-2010
    Location
    London
    MS-Off Ver
    Excel 2007, Excel 2010
    Posts
    56

    Re: Putting value instead of formula itself using VBA code

    Thanks Tom. Evaluate gives the value but blocks the formula from changing as one moves down the rows. So it is pasting the evaluation of Row 2 in all the other rows also. How can I overcome this?
    Reply With QuoteRegister To Reply
  4. 12-12-2011, 02:55 PM #4
    arlu1201
    arlu1201 is offline
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,167

    Re: Putting value instead of formula itself using VBA code

    Use the formulaR1C1 as you are using and then paste special values.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]
    Reply With QuoteRegister To Reply
  5. 12-12-2011, 03:19 PM #5
    goels
    goels is offline
    Registered User
    Join Date
    10-31-2010
    Location
    London
    MS-Off Ver
    Excel 2007, Excel 2010
    Posts
    56

    Re: Putting value instead of formula itself using VBA code

    I can indeed let the formulas be pasted and then copy the results and do a paste special on a different column but I want to tweak the existing code so that it puts the calculated results i.e. values in the first place rather than the formulas..
    Reply With QuoteRegister To Reply
  6. 12-12-2011, 03:31 PM #6
    arlu1201
    arlu1201 is offline
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,167

    Re: Putting value instead of formula itself using VBA code

    I have put the paste special inside your code itself. See amended code below -
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  7. 12-12-2011, 03:33 PM #7
    arlu1201
    arlu1201 is offline
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,167

    Re: Putting value instead of formula itself using VBA code

    You could even make it better as follows - 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  8. 12-16-2011, 09:52 AM #8
    goels
    goels is offline
    Registered User
    Join Date
    10-31-2010
    Location
    London
    MS-Off Ver
    Excel 2007, Excel 2010
    Posts
    56

    Re: Putting value instead of formula itself using VBA code

    Arlette...works brilliantly! Thank you
    Reply With QuoteRegister To Reply
  9. 12-16-2011, 10:11 AM #9
    goels
    goels is offline
    Registered User
    Join Date
    10-31-2010
    Location
    London
    MS-Off Ver
    Excel 2007, Excel 2010
    Posts
    56

    Re: Putting value instead of formula itself using VBA code

    Just realised when I use an array formula as below it just pastes the first calculated value 4999 times. How can I change the code to correct this?:

    Please Login or Register  to view this content.
    Thanks
    Reply With QuoteRegister To Reply
  10. 12-16-2011, 11:58 AM #10
    huuthang_bd
    huuthang_bd is offline
    Valued Forum Contributor
    Join Date
    12-14-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2007
    Posts
    439

    Re: Putting value instead of formula itself using VBA code

    Hi goels
    You put the formula in to first cell then fill down and then paste value
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  11. 12-16-2011, 12:41 PM #11
    goels
    goels is offline
    Registered User
    Join Date
    10-31-2010
    Location
    London
    MS-Off Ver
    Excel 2007, Excel 2010
    Posts
    56

    Re: Putting value instead of formula itself using VBA code

    Thank you huuthang - works perfectly
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1