Looping a macro, filtering

**mettekr** · 10-27-2008, 03:28 PM

Hi everyone. I have created a macro to filter results from a set of data, paste that data to a new file, and then create charts based on that data. Right now, my macro is basically multiplied 20 times to do this for each heading. Is there a way to create a loop that will just redo the same macro but for the next data heading? Here is a sample of the first part of the macro:

Please Login or Register  to view this content.

Then it repeats for the next one, but with USAU in place of USAE and Book2 in place of Book1. It doesn't even all fit in one module, I had to make two. And it's really a pain if I want to make an edit to the code. I'd love some help.

Thanks

**MatrixMan** · 10-27-2008, 03:43 PM

To be perfectly honest ... this is a bit of mess

But fret not! I see what you're trying to do; this is one of the limitations of the record feature - great for discovering the properties & methods you need, not so good at repeating things or doing things in the most efficient way.

To really deliver a solution that will be robust, I need to see your full workbook ... if you attach it I'll take a look & re-code this for you.

Hope this helps. MM.

**mettekr** · 10-28-2008, 09:25 AM

MatrixMan, you may end up a hero. And I'm quite new to VBA, recording is about the best I can do, then adding a few things myself to make it seem like I know what I'm doing :-). Here's the workbook.

Thanks a ton

**MatrixMan** · 10-28-2008, 11:00 AM

LOL ... well, happy to help

Quick question for you: are you sure you want the charts & data displayed within a sheet as you've done it in the example? i.e. would you prefer the charts established as separate chart sheets? If you just want it exactly per your example that's fine ... just let me know if you've done it that way for a reason ...

**mettekr** · 10-28-2008, 11:56 AM

That was just they way I happened to make it. These are going to go into two quadrants of a powerpoint slide, so I put them there because they stayed small. As more months of data start coming in, having it on a new sheet may be smarter. Also, I probably should have clarified since I had my first version of the macro in there too. The on I run is "Do_All" which calls Modules 2 and 3. Did my "lastrow" variable make sense? In this case you'd put 10 so that the charts are made with data through row 10...probably a better way again, but that was a variable I knew at the time.

Thanks again1

**MatrixMan** · 10-28-2008, 12:16 PM

OK - no problem ... so I'll put all the charts into a single sheet then; and your last row thing was a bit of a puzzle - I've determined the full range for each lane ID dynamically ... I'm almost done; should be able to post it within the hour. Final question: did you not want to chart the last column "MTD SCOS" on purpose? Easy fix .. just let me know.

**mettekr** · 10-28-2008, 12:29 PM

The MTD SCOS I only want used in the second chart that has 2007 SCOS, 2008 SCOS (June), MTD SCOS, and then 2010 Goals as the line accross. As for "I've determined the full range for each lane ID dynamically"...that's why I'm posting questions and you're posting answers :-)

**MatrixMan** · 10-28-2008, 02:43 PM

hi - see below ... paste these into the sheet object that has your data in it.

I've done this in the attached workbook, which I suggest you use to test first; NOTE: it will delete any sheets & charts other than the main one and the SCOS one so if you don't want this to happen, add the names to the array "varKeepSheets" (shown in red below).

Please Login or Register  to view this content.

And this one creates the charts in a new sheet for each ..

Please Login or Register  to view this content.

Hope that helps & points you in the right direction. MM.

**mettekr** · 10-29-2008, 09:37 AM

MM,

This is great! I tested it and it works. The only thing I'm trying to change now is the chart type and axis. Like on my originals, I need "RTS to FPS", "FPS to POEX", "PORT IDLE", and "OCEAN TRANSIT" stacked and on the left axis. "2010 Goal" also on the left axis, but as a line graph. Then "Machine Count" on the secondary axis as a line graph. You have the latter, I just thought I'd put it in there. And for these charts, we don't need 2007 and 2008 Days. We'd want a second chart for each lane that is a bar graph with 2007 Days, 2008 Days, and MTD SCOS. I may be able to do that myself, but who knows. I'm going to poke around in the code and see how well I can screw it up :-).

Thanks for all of your help!

**mettekr** · 10-29-2008, 09:44 AM

Ok, I've been working quickly. I found where to make the chart stacked, so that's all good, I just need to change the range for the first chart (exclude Rank, 2007 Days, 2008 Days, and 2010 Goal) and then basically duplicate the process to make the second chart with the last three fields I just said to exclude. I like where this is going.

**MatrixMan** · 10-29-2008, 10:46 AM

Good stuff ... best way to learn is to poke around a bit within a framework

Let me know if you get stuck anywhere & I'll see if I can point you in the right direction ... And of course once you're happy that your issue has been address satisfactorily, don't forget to set the post status = SOLVED.

**mettekr** · 10-29-2008, 10:58 AM

Oh ok, this is my first posting so thanks for letting me know. As for what's left...I have it all set for what I need for the first chart, but with all of those variables and craziness going on, I'm not sure how to make another graph for each lane with just 2007, 2008, and MTD. I'm sitting here staring at code and I feel like I'm not doing anything (I'm sure I look that way too). I think I'll need some direction there...
Also to clarify, on this second chart, there is no date range. Just the values for 2007 Days, 2008 Days, and the last value for MTD SCOS as bars. Then the 2010 goal as a line across the 3 (so only 3 values from that one to span the whole chart). Did that make sense?

**MatrixMan** · 10-29-2008, 11:56 AM

hmmm ... not really; perhaps if you manually set up the charts you want the end result to be and I'll be able to see what you're doing from there?

**mettekr** · 10-29-2008, 01:27 PM

Ok, I added a tab called 2nd chart sample to show what I meant. Looking back on what I typed, I'm not surprised it didn't make sense...
The other tabs have the charts the way I needed them too (partially done by a workaround you'll see in the code until I figure out the range settings)

**MatrixMan** · 10-29-2008, 05:14 PM

Hi again ... a few things that might help:

1. I can't really see what you're trying to do with the 2nd chart; the primary data is charted for just 1 month, while the target is over 3 months and incorrectly laid across the three primary data points. Is this a workaround or what you actually want?

2. Regarding your workaround commented in the code, try using the names associated with the series collection; you're currently deleting all series above the 6th and then formatting the 6th one. If you are sure this is what you want, you could use something like:

Please Login or Register  to view this content.

But it's preferable that if you really want to do this, you select each range you want to delete explicitly (I've stored the names in the array for you, so just cylce through it until you get to the one/s you want). Alternatively ... just don't include the data in the range in the first place.

3. You might find the code below useful for repositioning your charts within the available window.

Please Login or Register  to view this content.

Hope that helps. MM.

**mettekr** · 10-29-2008, 08:05 PM

Ok, I'll test out what you've got there when I get back to work in the morning. As for the format of that second chart, it is a weird way of getting what I want...the 2007 and 2008 data is going to be the same for a given lane everytime, and when I update the MTD SCOS on the MTD SCOS tab, it will change the MTD SCOS column for every lane, so that will be the same for the lane too in a way. So what that 2nd graph will show is how many days the transit took in 2007, how long the average was for the first half of 2008 (the June number), and then the average (MTD SCOS) since July. Then the data going across the 3 is the 2010 goal. I guess having that go across the 3 is a workaround for showing a benchmark. Don't know if that cleared anything up or not. I'll check back in tomorrow.