Using array in for next loop
Hello,
I need assistance using each value in an array to filter and then run code. Could someone please supply the basic "for - next" loop code showing how to use an array to repeat the same code several times? Thank you in advance for your assitance.
Or
Please Login or Register to view this content.
Please Login or Register to view this content.Please Login or Register to view this content.
Last edited by AB33; 10-19-2012 at 10:27 AM.
Thank you for the info. But due to my lack of knowledge I have another request. How do I incorporate "myArray" in the loop so each element of the array is chosen during each loop?
See amendment to my first post
Something like this perhaps.
Please Login or Register to view this content.
So this will work if MyArray = (Abbeville,Aiken,Liberty). And shouldn't I place the MyArray = statement after the Dim statement?
I thought you already had some code to set up and populate your array?
Yes I do. I apologize. I misinterpreted your last post. I will use the info you provided and let you know the result. Thank you very much for your assistance.
No problem.
What dimenstions are your array?
The code I posted is for a one dimensional array so will work with something like this.
Please Login or Register to view this content.
Just FYI--I'm very inexperienced. I record macro's and I try to tweak them for my purposes.
Anyway, my array setup is this: Myarray = range("c2:c13"). Then I used strFilter like this in the loop: ActiveSheet.Range("$A$1:$c$13").AutoFilter Field:=3, Criteria1:= strFilter. But suspect that's wrong because I keep getting a "subscript out range" error and the code strFilter = myarray(I) is highlited in the editor. Would it help if I posted all the code--it's about 30 lines and I'm not sure how to tag it.
When you create an array from a range like that you end up with a 2-dimensional array.
What exactly do you want to loop through?
The range you are using has 3 columns and 13 rows, which makes 39 values.
Do you want to use each of those 39 values for filter criteria in the loop?
No. I'm filtering on the 3 unique values in column C and copying the data to a new workbook. I was successful when I setup my array as originally discussed( MyArray = array(abbeville, ............). I do need to use the MyArray = range(a2:c13) because I have so many unique values to filter on. Would you be so kind as to provide the setup for that type of array? I very much appreciate your assistance and patience.
Why do you need to use an array?
Also, if there are only 3 unique values in column C, why 13 rows?
Sorry for the questions but I'm just a bit confused as to what you are actually trying to do.
No problem...ask all the questions you want. This is just a sample of a larger project. I have 140 unique values to filter and 40,000 rows of data. I have to copy the data for each filter value to a new workbook. There will be 140 workbooks when the project is completed. If I can reference a range(the column with the values that need to be filtered) it would be much more simpler. The main workbook already contains the column to be filtered. Maybe this will help----I have 40,000 equipment serial #'s(column A) that is owned by 140 different owners(column c in this example). I need to filter by owner and copy that data to a new workbook.
That sounds quite straightforward and it's probably easier to reference a range.
If posting code please use code tags, see here.
Will it always be true that the owner will be on the same row? If this is the case, I think you want something that will filter by owner (column c) and the copy all the values that are in column A to a new workbook named (column c). Is that correct?
So do I need to change any of the code to setup the array for a 2 dimension?
sorry.....didn't see your last post. Yes, that's correct.
Any of what code?
Also, what array?
You should use a range.
See the attached file for an example of how to use Advanced Fiiter to generate workbooks based on unique values from a particular column.
The intial code to set up an array that references a range. I believe it would look something like......MyArray =Array range(c2:c13)??
Why do you need an array?
If you wanted to loop MyArray you would need something like this.
If you wanted to loop the range.Please Login or Register to view this content.
Please Login or Register to view this content.
Norie, Please forgive my ignorance. I cannot adapt the last you recently sent. I do not understand it. As I said earlier
I am not a programmer. Although my code is crude and bulky, I can understand it and I know what it's doing. You would probably
get a good laugh if you saw it. Thank you for your patience.
My worksheet has a column with all the unique owners(one occurrence each). I will be referencing that column to populate MyArray.
to pass to the filter. I have adapted the code you sent me early on in this thread and it worked(see below). I need to be able to
populate the array using a reference to a range instead of the format you see below..."array(abbeville, aiken,)
this is the code you sent me which currently works:
Please Login or Register to view this content.
Last edited by Cutter; 10-20-2012 at 09:17 PM. Reason: Added code tags
What can't you adapt?
Why do you insist on using an array when it's easier to directly reference the range with the unique values?
I prefer to use the array because I understand what my code is doing. If there is a problem I would not know where to begin to correct it if I use the module you sent. I know it's more efficient and easier(for a VBA programmer) but I'm more comfortable using my code.
Oh....and if I can use the range to populate the array it would be almost the same thing...the only difference being using my crude and simple code.
You have code?
You haven't posted any here.
Yes. But you must promise not laugh!! I just need to read up on the proper method to post code on this site. You know....the tags and such.
I promise I won't laugh but you really should have posted the code earlier, no matter how bad it was.
It would have given us a clearer idea what you want to do.
By the way, did you look at the workbook I attached?
I have attached the sample files.
I was able to change 3 letters and a sheetname in the code from the workbook I attached to get it to work with your data.
Please Login or Register to view this content.
Thank you Norie for all your help.
If you are satisfied with the solution(s) provided, please remember to mark your thread as Solved.
New quick method:Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
Select Thread Tools (above first post on page) -> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Or you can use this way:
Go to the first post
Click edit
Click Go Advanced
Just below the word "Title" you will see a dropdown with the words "No prefix".
Change to "Solved"
Click Save
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks