How do I structure a Do/Loop to loop all rows starting from row 3? I declared a variable for the rows (intRegion), but I don't know how to make the loop run through all of the rows without typing in the input boxes by hand. My rows are sorted by state, but I would like it to loop for all of one state, save the file, and then repeat the same process for all other states.
Thanks for the help!!
Please Login or Register to view this content.
How will VBA be able to tell when the data for a state ends and the data for the next state starts?
If posting code please use code tags, see here.
Thanks for your reply. That's what I've been having trouble with. My idea is to loop until, for example, cell A3 <> A4. And when that happens, I'd like it to save as a new file and then continuing doing the same thing with the next state.
As of now, I've been using an input box to tell vba when to start and stop, but that's not too efficient if I need to run the code multiple times.
Can you upload an example workbook?
Click on GO ADVANCED and use the paperclip icon to open the upload window.
Hi,
I'm not able to upload workbook, but hopefully this may look a little better to show the structure of my code...Sorry for the inconvenience
I have declared all of my variables but I just have an issue of how to use them with a Do Loop Until statement
intState to intLast are looking at rows 14 to 17, but is there a way to make it loop through all of my rows using a Do Until Loop with having to define the rows I want it to look at (so essentially getting rid of the variables, intState and intLast)? Or is there a better way for me to look at this? The bold in my code shows what I would like to perform.Please Login or Register to view this content.
Thanks!
Last edited by LLL0422; 06-04-2014 at 09:05 AM.
You could use a loop to go through all the rows but without seeing how your data is structured it's hard to give specifics.
Even knowing what column the state is in would help.
Sorry, the states are listed in the 3rd column, and the rows are sorted by state. So ideally, I'd like the code to loop until the state changes, save the template, and then go through loop again until state changes, save the template, etc. I hope that helps... Initially I had an input box which was only able to loop one state at a time, but I hit an obstacle when I tried to loop through multiple states because the code couldn't recognize when the state changes.
Last edited by LLL0422; 06-04-2014 at 09:18 AM.
All you really need is a loop to go through all the rows.
Please Login or Register to view this content.
Thanks so much! So what I am filling out used to follow this code:
Would it be correct to have it like this?:Please Login or Register to view this content.
Because I am pulling info from each row and then moving to the next row, is rState able to specify what row I'm using?Please Login or Register to view this content.
Also, I am getting runtime error 1004: "Method 'Range' of 'object' '_Global' failed" for the "For Each" code
Sorry I'm not really sure what you are asking - for example, what's rState?
In the code I posted cl is the cell that has the state, you can get it's row using cl.Row.
Sorry, instead of cl, I changed it to rState.
Is this what you mean by cl.Row? And is this okay to specify what row it should look at?
Sorry for confusion, I'm super new at VBA and am still trying to find my way to understand what to do.Please Login or Register to view this content.
That would do it, though you could also use this.
This tells VBA to put the value that is offset 6 columns to the right of rState into dtEnd.Please Login or Register to view this content.
So if rState was referencing C10, rState.Offset(,6) would reference I10.
By the way, when using Cells you can use either the letter or number to specify the column.
So you could use this.
Please Login or Register to view this content.
Good to know!
Also within my "For each" loop, I am also looping what I'm calling intPlan, which loops from columns 12 to 35.
Is this okay to specify the columns?:
And is there a way to fix the range error that i get from this?:Please Login or Register to view this content.Please Login or Register to view this content.
If that gives you the right columns then that should be fine.
The error is my fault, the parentheses aren't closed properly, Row shouldn't be there and no sheet is referenced.
I'm not quite sure which sheet you are working with but if it's Sheets(strSheet) then you could try something like this.
Please Login or Register to view this content.
Ah okay, I'll remove Row from it.
The loop works great for the first row, but is there a way to make it not open my template again when its populating for the other rows?
Also, the loop for "intPlan" also keeps recognizing that intRowKnt = 14 when its reading a new row, but it should be continuing where it left off.
Please Login or Register to view this content.
Last edited by LLL0422; 06-04-2014 at 11:35 AM.
There's no code that's 'opening' the template, but there is this,which activates the template.Please Login or Register to view this content.
There's no need to activate a workbook/worksheet to copy/paste data.
I would have something like this before the loop, perhaps even right at the top of the code.
Then you can use something like this.Please Login or Register to view this content.
By the way, I removed this because, as far as I can see, it wasn't really doing anything.Please Login or Register to view this content.
Please Login or Register to view this content.
It seems likeis giving me a mismatch error for some reason..Please Login or Register to view this content.
Please Login or Register to view this content.
Last edited by LLL0422; 06-04-2014 at 11:55 AM.
Definitely a type mismatch error and not a subscript out of range?
Yep, definitely Type mismatch.
This is the code that I have so far. Hope this helps.Please Login or Register to view this content.
Last edited by LLL0422; 06-04-2014 at 12:07 PM.
Sorry I can't see why that's happening, it works fine for me.
How have you declared strTemplate?
Could there be something wrong with my code?
Please Login or Register to view this content.
The problem could be that strTemplate doesn't actually have a value until a little bit further down in the code.
By the way, this won't work - see the code in post #17.
Please Login or Register to view this content.
Ah, so should it look something like this?
Please Login or Register to view this content.
No, more like this.
Or even this.Please Login or Register to view this content.
By the way, which worksheet/workbook is column C in here?Please Login or Register to view this content.
Please Login or Register to view this content.
It is in the IDFile workbook. Template is a separate workbook.
Ah now it says out of range if I put it after declaring what strTemplate is.
Is the template workbook open?
This code will refer to column C in the active sheet, whatever that is.
As will all the ranges in this section of code.Please Login or Register to view this content.
Please Login or Register to view this content.
The code should open the template workbook.
My issue was that when it loops the plans, it would keep trying to reopen the template.
How do I condition the code so that it only opens once per state?
The code used to be:
Please Login or Register to view this content.
Last edited by LLL0422; 06-04-2014 at 12:38 PM.
That wasn't in the earlier code where you mentioned the template being opened again.
Try putting this at the top of the code.
It might also be an idea to create a reference to the IDFile workbookPlease Login or Register to view this content.
For example, if the IDFile is open.
Then you can make sure you are referring to the correct workbook and worksheet here.Please Login or Register to view this content.
Please Login or Register to view this content.
Sorry, I made some adjustments and then posted the code that I had at the moment. I will try what you suggested.
I found a different approach where I can use a function to test if the sheet is already open and it seems to work! But I did Dim wbTemplate as Workbook also.
One last thing..hopefully,
C3: ALPlease Login or Register to view this content.
C4: AL
C5: FL
This works great, except that once the code recognized that C4 <> C5, it goes straight to saving the file rather than completing the template with C4's information.
Thank you for bearing with me!!
Is there no way you could mock up an example data workbook and template and attach them here?
That would make it a lot easier to give help with the code.
As it is I'm going on the code you are posting.
PS If the whole point of the code is to split up the data by state then there could be other(better?) methods.:l
I'm really sorry, I can't upload an example workbook at the moment.
As for my data, all of the states have to be on the same worksheet, and I need to somehow make it create a separate template for each state all at once. Originally, I would run the code state by state, but for time's sake, that's not too efficient. Sorry for trouble!
I think I've gotten most of my questions answered, but I will make new thread for other questions not related to this one.
Thanks so much for your help! I really appreciate it!
This should actually be a very straightforward task and you'll find plenty of examples if you search the forum.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks