+ Reply to Thread
Results 1 to 7 of 7

Converting/Pasting non-contigous ranges of formulas created by VBA to values

  1. #1
    Valued Forum Contributor ron2k_1's Avatar
    Join Date
    09-30-2009
    Location
    Not the USA
    MS-Off Ver
    Excel 2003, 2007
    Posts
    606

    Converting/Pasting non-contigous ranges of formulas created by VBA to values

    Hi crew,

    This is my code (works perfect):
    Please Login or Register  to view this content.

    The code goes through up to 15 tabs, the prob is that this part of code takes up to 2:26 secs to execute:
    Please Login or Register  to view this content.

    I've tried this other variation takes 1:58 min; still too long:
    Please Login or Register  to view this content.
    Now housecleaning: I've asked something similar before earlier this week. I bumped it yesterday and now its burried in pages of posts. So I need your wisdom, I just need to find some faster way or turning all those VLOOKUP formulas into values.

    Thanks
    Last edited by ron2k_1; 03-07-2011 at 05:26 PM.
    Ron
    Knowledge is knowing that a tomato and pepper are fruits. Wisdom is knowing whether to put these in a fruit salad

    Kindly

    [1] Use code tags. Place "[code]" before the first line of code and "[/code"]" after the last line of code. Exclude quotation marks
    [2] Mark your post [SOLVED] if it has been answered satisfactorily by editing your original post in advanced mode.
    [3] Thank (using the little scale) those that provided useful help; its nice and its very well appreciated

  2. #2
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: Converting/Pasting non-contigous ranges of formulas created by VBA

    Try this:
    Please Login or Register  to view this content.

  3. #3
    Valued Forum Contributor ron2k_1's Avatar
    Join Date
    09-30-2009
    Location
    Not the USA
    MS-Off Ver
    Excel 2003, 2007
    Posts
    606

    Re: Converting/Pasting non-contigous ranges of formulas created by VBA

    Oh yeah, I had already tried that as well, that for sure is fast, but it placed the value of:
    Please Login or Register  to view this content.
    To all the cells in UpdFAll2

    Thanks, but any other takes?

  4. #4
    Valued Forum Contributor ron2k_1's Avatar
    Join Date
    09-30-2009
    Location
    Not the USA
    MS-Off Ver
    Excel 2003, 2007
    Posts
    606

    Re: Converting/Pasting non-contigous ranges of formulas created by VBA

    Any other suggestion as to how to convert the formulas in those non-contiguous ranges to values guys?

    Thanks,

  5. #5
    Valued Forum Contributor ron2k_1's Avatar
    Join Date
    09-30-2009
    Location
    Not the USA
    MS-Off Ver
    Excel 2003, 2007
    Posts
    606

    Re: Converting/Pasting non-contigous ranges of formulas created by VBA

    It's me again guys. Any other suggestion to convert the formulas in UpdFAll2 to values other than those that I already tried.

    I'll appreciate it a ton.

    Thanks

  6. #6
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Converting/Pasting non-contigous ranges of formulas created by VBA to values

    Rather than iterate on a Cell by Cell basis why not iterate by Area ?

    Please Login or Register  to view this content.
    edit: for the record I've not looked at the code in any depth but you might want to read through: http://blogs.msdn.com/excel/archive/...practices.aspx
    Last edited by DonkeyOte; 03-07-2011 at 10:27 AM.

  7. #7
    Valued Forum Contributor ron2k_1's Avatar
    Join Date
    09-30-2009
    Location
    Not the USA
    MS-Off Ver
    Excel 2003, 2007
    Posts
    606

    Re: Converting/Pasting non-contigous ranges of formulas created by VBA to values

    Hey DKO, nice! I could have sweared I had tried that one too; or maybe not; now that I recall it was probably .Ranges and not .Areas.

    Thanks a mil. This is definitely an improvement. From 1:58min to 34secs per loop/worksheet. Awesome!

    I'll mark the thread solved now!

    Thanks,

    Ron

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1