+ Reply to Thread
Results 1 to 5 of 5

Loop until condition is met

  1. #1
    Registered User
    Join Date
    03-08-2011
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    3

    Thumbs up Loop until condition is met

    Hi,

    I recently have been trying to write a macro which will copy the ranges A1:37, paste it a few rows below and then copy B1:37, paste it in the column alongside the copied A values and keep looping this process(keeping the A1:37 but changing B to C, D, ect until it reaches QI).

    As it's quite hard to explain, here's the recorded macro of the intial process. I would like to know the commands which will change B into a variable so I can keep moving one column along; pasting it below the previous pasted content- and the correct IF syntax to end the macro once it reaches QI.

    Many thanks

    Please Login or Register  to view this content.
    And here's mine
    Please Login or Register  to view this content.
    Last edited by Toimia; 03-08-2011 at 08:40 PM.

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Loop until condition is met

    Quote Originally Posted by Toimia View Post
    I recently have been trying to write a macro which will copy the ranges A1:37, paste it a few rows below and then copy B1:37, paste it in the column alongside the copied A values and keep looping this process(keeping the A1:37 but changing B to C, D, ect until it reaches QI).
    I can do this, but first can you explain why you want to do it one column at a time? Why not just copy the whole range A1:QI37 to the desired destination in a single command?
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    03-08-2011
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Loop until condition is met

    Quote Originally Posted by 6StringJazzer View Post
    I can do this, but first can you explain why you want to do it one column at a time? Why not just copy the whole range A1:QI37 to the desired destination in a single command?
    Long story short, it's part of a customer excel document which was imported from a SQL database dump. 'A' column contains the headers to the certain fields which need to be copied first before copying the data in the other columns(each column after 'A' has the customers details in). So the final chunks(A+B, A+C, A+D ect) can be copied across into a word document.

    If you could do this asap I would greatly appriciate it. You would've saved me so many hours of work

    - Toimia

  4. #4
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Loop until condition is met

    I didn't follow your explanation or your code but I'm starting to get the idea that this is what you want to do.

    Lesson 1 is that code generated by the macro recorder can be instructive but is usually pretty bloated and inefficient.

    Not sure what you're trying to do with formatting but I tried to replicate what you had.

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

  5. #5
    Registered User
    Join Date
    03-08-2011
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    3

    Thumbs up Re: Loop until condition is met

    Yeah that's perfect, thanks man! (I'm also doubly appriciative that you replicated the formatting, good job! )



    - Toimia

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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