Passing Variables within sub

[scottc_00]

I have a problem with a small part of a lager procedure.

I have a sub that will create a file for Group1 and then save it in Group1 drive location
then loop to Group2 create a new file and save the file in the Group2 drive location
etc

Here is the offending code

Please Login or Register  to view this content.

so storepath resolves to "Group1Path". What I want is "XXX".

I tried

Please Login or Register  to view this content.

that didn't work. it caused Error2029.

I also tried using public variables

Please Login or Register  to view this content.

still no dice. Anyone have any idea how I can use variables in this way?
or alternately, how I can set this up to create a new path based on which
Group I am dealing with for each iteration of the loop?
before anyone asks, I am sorry but I can't post my file.
I work for a large corp and I am dealing with sensitive data.

thanks

[MrShorty]

Yes, but you can't "concatenate" a variable name like that. The usual first suggestion is to use arrays.

Please Login or Register  to view this content.

[scottc_00]

MrShorty, Thanks for your reply. I guess I misstated my issue a little bit. the concatenation is the key.
Even though I wrote the sample code to use a Group number that's not really possible in practice.
The Groups are not numbered. I used numbers to represent the concatenation. sorry for the confusion.

Please Login or Register  to view this content.

What it does is spin thru a list of Colors (Businesses), creates the file and then tries to save it.
Thanks for the info that concatenation is not allowed, but knowing that, I need a new approach.
Any idea how would you resolve this issue?

Thanks

[scottc_00]

the only options I can think of is to abandon the loop and just write a separate code block for each business.

Please Login or Register  to view this content.

but this will quickly get out of hand since I have 14 Businesses to deal with.

Any suggestions?

[scottc_00]

I have resolved the issue but it feels like a brute force method. Hopefully someone has a better suggestion
Here's what I've done.

Please Login or Register  to view this content.

but as I said I have 14 businesses to deal with so it's a little rough.

Thanks for any help.

[MrShorty]

14 blocks of identical might be easiest -- if the more advanced coding concepts that might come up are above what you understand and above what you want to learn about. My mentor taught me that you want to write code that is easy for YOU to write, easy for YOU to debug, and easy for YOU to change and maintain -- even if that is not the same as other, more advanced programmers would do.

If you are interested in learning and expanding your coding skills, this is how I probably would do it (with what I know now). I would probably use a "lookup array" that would allow me to associate an index number with a color. Something like:

Please Login or Register  to view this content.

I believe there are more advanced coding concepts (user-defined types or scripting dictionaries) that can also do this sort of thing, but I don't know enough about those concepts to comment. I will invite others to make suggestions if there are other concepts that would help.

[scottc_00]

MrShorty, Thanks for your reply. I always want to learn more advanced concepts. If I understand it correctly you set up an array of the colors and then colorindex selects a color based on the iteration of the loop. first time thru it selects blue, second time thru it selects yellow, etc. That's the Match part, right? That might actually work for me because the order of the colors wont change, but what if they were random?

[MrShorty]

Not exactly. If you are always looping through all colors and always in the same order, then you, the programmer, just need to keep straight in your head that index 1 goes with blue and index 2 goes with yellow and so on. the colorsarray array and the match function are probably not needed. I had assumed before the last post that you were talking about "randomly" accessing the elements of the array, using the colorsarray and the match function to connect the color to the index number depending on the value assigned to color.

[MarvinP]

Hi Guys,

This looks like the VBA Split() function might be easier to use and maintain. Also you can use the UBound() to tell the upper limit of your for next loop.
http://www.exceltrick.com/formulas_m...plit-function/

[ptmuldoon]

Perhaps I'm off base, but can you confirm the file paths to the save the file actually exist? I'm not certain on Excel's behavior but when do a SaveAs, doesn't Excel then change to that new folder location as the active path?

So is it possible that based on the original code, it will Save to the first folder of XXX. But now Excel see's XXX as the active folder/directory and can't find YYY?

I think MrShort's suggetion of setting up an Array to store the values and then loop they array may be the easiest solution?

Or you could also maybe list all of your colors/filepaths on worksheet and then loop that range?

[Kyle123]

You just need to use a Collection, you should also be declaring your variables really

Please Login or Register  to view this content.

[scottc_00]

Thank You MarvinP, ptmuldoon, Kyle123. I will look into the split function and collection. Happy to learn new things. I am curious about ptmuldoon's comment. currently, I am using one test location because I don't know all the final locations yet. when I add the various locations will it fail? I will have to test that today. Thanks pt. I will reply a little later today with my findings on all 3.

[scottc_00]

I read about the split function. Looks interesting and I will likely use that in the future.
Not really sure how I can use it for this issue, can you explain?

as for using UBound for the upper limit of the ForLoop iterations.
I added this

Please Login or Register  to view this content.

This works great as long as my pivot table is displaying all of the pivot items.
I have one table that shows all businesses ptB and one that has several hidden businesses ptT.

for ptB the UBound limit is 9 and I need 9 businesses
for ptT the UBound limit is 25 but I only need the 5 businesses displayed in the pt.

How do I reconcile this?

I tried

Please Login or Register  to view this content.

but of course that failed.

Looking into Collection next.

Thanks

[scottc_00]

I set up the code for the Loop like this.
Probably not the cleanest way and I intend to still investigate the Collection and array ideas suggested.
but for now, and to answer the question about Excel behavior for Save As and not being able to find the dir.
I think because I used the Full Path, the prob doesn't exist (at least in this version)

Please Login or Register  to view this content.

One thing that is confusing me is in this part...

Please Login or Register  to view this content.

This only works if the items are sorted alphabetically AND ALL items are visible.
this line

Please Login or Register  to view this content.

the first time thru the loop it will use the first visible item, second time it uses the second visble item etc.

this line

Please Login or Register  to view this content.

the first time thru the loop it will use the first item (in alpha order) whether visble or not etc
I need to find a command that only considers the visible Pivotitems.
Looking into something like .Datarange.cells(i).parent or something. so far no joy.

[scottc_00]

I was able to get this array to work and it solved my other problem as well (different counting).

Please Login or Register  to view this content.

I will look into Collection next.

Thanks