+ Reply to Thread
Results 1 to 7 of 7

Copy dynamically changing column and Paste using VBA Loop (Loop within Loop)

  1. #1
    Registered User
    Join Date
    02-06-2013
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    3

    Copy dynamically changing column and Paste using VBA Loop (Loop within Loop)

    Hello,

    After spending too much time on this problem, I am hoping to find some help here - seems like the right place. This is the last bit of code out of a larger macro - which (except for this bit) works fine. I am attaching all of the code nevertheless. Briefly, this is what is going on:

    1) My sheet gets data from another sheet repeatedly (Loop 1)
    2) Then I run a Solver model iteratively to solve a bond math problem (Loop 2)
    3) Finally, each vector (column) of Solver solutions needs to be value-pasted in its own column (Loop 3). And this is the step where grief catches up with me.

    Both t and j need to loop around 300 times; whereas i is fine as it is.

    Any and all help welcome!!


    Please Login or Register  to view this content.
    Last edited by nixon72; 02-12-2013 at 05:47 PM. Reason: Added code tags, as per forum rules. Don't forget!

  2. #2
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Stockholm, Sweden
    MS-Off Ver
    Excel 2003, Excel 2007 & reluctant Excel 2010 user
    Posts
    4,593

    Re: Copy dynamically changing column and Paste using VBA Loop (Loop within Loop)

    Not sure I understand your Solver loop. As far as I see i goes from 34 to 52 and you run Solver for every increase of i but then you seem to throw away the result because after the line
    Please Login or Register  to view this content.
    you just calls for the next i value.

    If this part is supposed to capture the Solver result
    Please Login or Register  to view this content.
    it should be place after
    Please Login or Register  to view this content.
    and before
    Please Login or Register  to view this content.
    Alf

  3. #3
    Registered User
    Join Date
    02-06-2013
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Copy dynamically changing column and Paste using VBA Loop (Loop within Loop)

    Alf, the trick is that the whole column - O34 to O52 - is ONE set of results. And that needs to be copied over to its own column before looping to the next t. Right now, the trouble is that it only captures the last iteration of t, and keeps overwriting the new column (i.e. where the Solver results are being copied). Hopefully this will now make sense.

  4. #4
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Stockholm, Sweden
    MS-Off Ver
    Excel 2003, Excel 2007 & reluctant Excel 2010 user
    Posts
    4,593

    Re: Copy dynamically changing column and Paste using VBA Loop (Loop within Loop)

    No I'm not really sure I understand it. Is it possible to upload the file?

    Alf

    Ps Just make sure that there is no "sensitive" information in the file if you upload.

  5. #5
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Stockholm, Sweden
    MS-Off Ver
    Excel 2003, Excel 2007 & reluctant Excel 2010 user
    Posts
    4,593

    Re: Copy dynamically changing column and Paste using VBA Loop (Loop within Loop)

    Looks like your reply got duplicated several times
    so it did. I do wonder me or Forum hickups?

    The final result is the WHOLE column, and that needs to be copied over to S10:S28. This is what the j index is attempting to do, but apparently it fails.
    I don't think so. To me it does what it's told to do but that may not be what you want. At the moment j loops from 1 to 3 and copies the solver
    result with a row offset of -24 and a column offset of j + 3 so result is copied to column 4, 5, and 6 from the Q column i.e. S, T and U column.

    Then j is reset to 1 and told to loop to 3 again. So if you wish to loop Solver 3 times keep the j loop but add anothe counter say k to be dim'ed as Integre and add a line after

    Please Login or Register  to view this content.
    Then modify this part
    Please Login or Register  to view this content.
    Alf

  6. #6
    Registered User
    Join Date
    02-06-2013
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Copy dynamically changing column and Paste using VBA Loop (Loop within Loop)

    Alf, this is PRECISELY what I needed! Thank you very, VERY much - you've saved me a lot of soul-searching (or code-searching). Keep up the excellent work!! All the very, VERY best!!!

    -Nick N.

  7. #7
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Stockholm, Sweden
    MS-Off Ver
    Excel 2003, Excel 2007 & reluctant Excel 2010 user
    Posts
    4,593

    Re: Copy dynamically changing column and Paste using VBA Loop (Loop within Loop)

    Glad to be of help and thanks for feedback!

    As this seems to take care of your problem you could click on my star (bottom left) and give a rating to my answer but even more important don't forget to sign your thread solved!

    Alf

    To mark your thread solved do the following:
    - Go to the first post
    - Click edit
    - Click Advance
    - Just below the word "Title:" you will see a dropdown with the word No prefix.
    - Change to Solve
    - Click Save

+ 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