+ Reply to Thread
Results 1 to 6 of 6

VBA to loop through cell-defined range and paste table output after each loop.

  1. #1
    Registered User
    Join Date
    07-28-2009
    Location
    Stavanger, Norway
    MS-Off Ver
    Excel 2003
    Posts
    28

    VBA to loop through cell-defined range and paste table output after each loop.

    Hello,

    I've attached an example of my problem. Not sure where to start - your help much appreciated in advance!

    Table 1 provides a unique ranking (driven from) elsewhere in my model but the cells will be formula based.
    Table 2 is what the macro aims to calculate with each loop (see formula in cells). The loop is to start at 1 and go until the value in cell F6.
    Table 3 is continually overwritten after each loop, (example output shown depending on cells B6 and G6).

    I couldn't find a clear example elsewhere so i really do appreciate your help!

    cheers,

    m
    Attached Files Attached Files

  2. #2
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    4,914

    Re: VBA to loop through cell-defined range and paste table output after each loop.

    Someone may post prettier code, but an option:

    Please Login or Register  to view this content.
    It could be consolidated so that the case statement is inside all the loops and only applies to the "If myArr(j, k)..." line, which is more compact, but would execute the select a lot more times and in theory is less efficient.
    Please help by:

    Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
    Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know

    There are 10 kinds of people in this world... those who understand binary, and those who don't.

  3. #3
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    4,914

    Re: VBA to loop through cell-defined range and paste table output after each loop.

    Thanks for the rep mscott123 If that means the issue is resolved, please mark the thread as solved?

  4. #4
    Registered User
    Join Date
    07-28-2009
    Location
    Stavanger, Norway
    MS-Off Ver
    Excel 2003
    Posts
    28

    Re: VBA to loop through cell-defined range and paste table output after each loop.

    Thanks Arkadi, so impressive!

    To be honest, I wasn't anticipating this approach.

    I think its solved by my VBA come up short and I'll need to try to figure out a couple of small changes, any further advice is most appreciated:
    I noticed blank table 1 cells were being picked up with 'a's in the output (*** case), how would I modify them just leave them blank?

    In reality, my actual table size is much larger and my secondary objective was to insert some additional actions with my model output after each new table 'punch-card' was generated.
    For example after the first 3x4 table is generating, I would copy and paste model outputs from and to elsewhere.

    Really appreciate everything
    Last edited by mscott123; 03-14-2019 at 05:24 AM.

  5. #5
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    4,914

    Re: VBA to loop through cell-defined range and paste table output after each loop.

    I guess the blank cells are only an issue in the Incremental version?

    Change:

    Please Login or Register  to view this content.
    To:

    Please Login or Register  to view this content.
    (in the For "k" loop)

  6. #6
    Registered User
    Join Date
    07-28-2009
    Location
    Stavanger, Norway
    MS-Off Ver
    Excel 2003
    Posts
    28

    Re: VBA to loop through cell-defined range and paste table output after each loop.

    thanks again!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Create loop, copy output, paste into new sheet
    By KBrown11 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-23-2016, 04:17 PM
  2. Possible to loop formatconditions.formula across a defined range?
    By mansfin in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-23-2015, 09:54 AM
  3. Loop formula to search through range to get expected output
    By raj2989 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-17-2014, 09:15 AM
  4. [SOLVED] Copy dynamically changing column and Paste using VBA Loop (Loop within Loop)
    By nixon72 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-12-2013, 12:46 PM
  5. Loop to Copy/Paste Defined Data
    By AlvaroSiza in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-22-2011, 02:31 PM
  6. Loop through worksheets which are defined in a range of cells
    By OilMan in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-23-2010, 02:37 AM
  7. help to create loop in a defined range
    By azegurb in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-10-2009, 03:24 AM

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