none
Excel 2010 vba Custom Insert Column(s) Loop

    Question

  • I have a macro set up, it reads from 3 sheets, drops the values in row 26, and merges the header cells accordingly in row 25.  Catch is, it's a run and done, only works once without having to add new stuff manually.  Example:

    |      Components     |     |   Pricing      |  |   Other  |

    | c1 | |c2 | c3| c4| c5|     |p1| |p2| |p3|   |o1|   |o2|   

    Not sure on the approach.  Apparently I need to find a way to iterate through the Components, if a new has been added on it's reference page, insert it as c6 in a new column on the same row between c5 and p1, and merge the cells on top to match, same for pricing and other.  It seems the cell references in the existing cells will auto-update, which saves on effort, but still not sure how to approach this code-wise.  Where should I start the loop and how do I compare what has already been added to what hasn't, without overwriting?  Everything so far was looking for blank cells using XLleft or XLUp.


    May the fleas of a thousand camels feast happily on the lower regions of your enemies. And may their arms be too short to scratch!

    Monday, March 18, 2013 7:41 PM

Answers

  • I don't know that anyone without access to your sheets and lots of time and selflessness can come up with the solution you want.

    On the other hand, if I understand what you're doing, in situations like this I just regenerate the final report from scratch each time data changes.

    Jim

    • Marked as answer by psifreak Tuesday, March 19, 2013 1:43 PM
    Tuesday, March 19, 2013 1:21 PM

All replies

  • Maybe a better visual can help.  I really can't figure this out, or even if it's possible.  A loop that will turn the top grid into the bottom, reading from the one in the middle, looking for new entries.  Doesn't matter what I've tried so far, it either writes over price1 or adds the new component after other4.  It has to insert the new column between E and F, and merge the new column with the header.


    May the fleas of a thousand camels feast happily on the lower regions of your enemies. And may their arms be too short to scratch!

    Tuesday, March 19, 2013 12:46 PM
  • I don't know that anyone without access to your sheets and lots of time and selflessness can come up with the solution you want.

    On the other hand, if I understand what you're doing, in situations like this I just regenerate the final report from scratch each time data changes.

    Jim

    • Marked as answer by psifreak Tuesday, March 19, 2013 1:43 PM
    Tuesday, March 19, 2013 1:21 PM
  • I was afraid of that.  Thanks for the input Jim.  Been playing with a for each row in the ref page, if it's value equals the component name on the final page column, skip, next row and column, until mismatch, insert there (no dice).  Tried a few do whiles, text and count comparisons....I figured I was just on the wrong approach, or missed a shortcut.  My intention for the design was exactly that, restart if they have to add things, or let them add a column themselves, but my colleagues insist that's not dynamic enough and don't trust the end users with unprotected worksheets.  Time to talk some sense at them then.  Greatly appreciated!

    May the fleas of a thousand camels feast happily on the lower regions of your enemies. And may their arms be too short to scratch!

    Tuesday, March 19, 2013 1:43 PM