+ Reply to Thread
Results 1 to 6 of 6

do while loop for range of cells

  1. #1
    Registered User
    Join Date
    04-22-2013
    Location
    Belgium
    MS-Off Ver
    Excel 2010
    Posts
    10

    do while loop for range of cells

    Hi,

    Currently I have following code:
    Please Login or Register  to view this content.
    Problem: I work with random values, which makes this "copy-paste story" rather long (time).

    How can I copy a value range? and paste it ? If this would work, it would make our macro like 18 times faster

    Any advice is welcome!
    Last edited by MeerskiD; 05-16-2013 at 03:26 AM. Reason: Added Code Tags

  2. #2
    Forum Contributor
    Join Date
    10-13-2012
    Location
    Southern California
    MS-Off Ver
    Excel 2007
    Posts
    397

    Re: do while loop for range of cells

    This isn't what you're looking for, but you did say "any advice is welcome."

    I would have written your piece of code this way:
    Please Login or Register  to view this content.
    It does the exact same thing, of course, but as you can see, it's a lot, lot shorter.

    If you put Application.ScreenUpdating = False at the beginning of your code, and Application.ScreenUpdating = True at the very end, your code should run instantly. What else do you have that's making it take so long to run?

  3. #3
    Registered User
    Join Date
    04-22-2013
    Location
    Belgium
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: do while loop for range of cells

    Edit: your code does work!

    For every value that gets pasted in the table, all values are re-calculated (the paste action triggers the random value calculation).
    The perfect solution would be copy + pasting the range values (26;6:24), then they calculate new values (because of the paste action); and the new value range is pasted again ...

    Still looking for the perfect solution though (copy range, paste range (row x), copy range, paste range (row x+1), copy range, paste range (row x+2),....
    Last edited by MeerskiD; 05-15-2013 at 03:06 PM.

  4. #4
    Forum Contributor
    Join Date
    10-13-2012
    Location
    Southern California
    MS-Off Ver
    Excel 2007
    Posts
    397

    Re: do while loop for range of cells

    Try this:

    Please Login or Register  to view this content.
    If desired, change the line that says Range(Cells(26, 6), Cells(26, 24)).Select and user variables for the cells, instead of the hardcoded numbers.

  5. #5
    Registered User
    Join Date
    04-22-2013
    Location
    Belgium
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: do while loop for range of cells

    thanks for this! we're getting close to the solution

    Please Login or Register  to view this content.
    I added this part of the code, now it's adding the values in the correct rows (and not one singular row).

    But currently they paste a formula, how can I copy the values? I tried following (but it doesn't work):

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    04-22-2013
    Location
    Belgium
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: do while loop for range of cells

    Solved:

    Please Login or Register  to view this content.
    thanks everyone for your input!

+ 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