Hi,
I am running into some issues when trying to run my macro. It's purpose is to grab data from a table and populate them into a separate excel template. For some reason, the code is able to save the templates I've created, but the templates have absolutely no data in them (but I see it work during Step Into). What could be some possible causes and solutions to this problem?
Thanks!
Can you upload an example workbook?
Click on GO ADVANCED and use the paperclip icon to open the upload window.
If posting code please use code tags, see here.
Hi, I can't upload a sample workbook, but this is my code:
I'm sure this could be cleaned up a bit, but for now, this is what I'm working with.
Please Login or Register to view this content.
This shouldn't be where it is in the loop.
You should first open the template outside the loop, then open it again after you've transferred each states data to the template and saved and closed.Please Login or Register to view this content.
Another problem I can see is that you should be copying the data to the template before you check to see if the next row will be a different state.
Which worksheet, in which workbook has the data anyway?
Is it 'All Plans' in 'ID File.xlsm'?
By the way, the flow of the code is pretty hard to follow - it's jumping about quite a bit because of the GoTos.
Yes, that's where my data is.
So how should it look if I am opening the template before checking and then reopening?
I've tried this before also, but it didnt seem to work with my previous code.
I think this is kind of how I would do the code.
Please Login or Register to view this content.
Thanks so much! I will work off of the code that you provided.
So when I go step by step, I'm reaching a type mismatch error for:
Please Login or Register to view this content.
My fault.
This,
should be this.Please Login or Register to view this content.
Please Login or Register to view this content.
Last edited by Norie; 06-05-2014 at 10:15 AM.
The PlanID, ChildRate and AdultRate are not populating correctly for some reason.Please Login or Register to view this content.
Last edited by LLL0422; 06-05-2014 at 10:22 AM.
Apart from that, does it work at all?
It works for PlanID = 1 but after that, it tries to overwrite the info that had already gone on the template instead of adding on to it.
I created a sample data table and template for you. These are in the same file for the sample, but I actually have the template in a separate file. Hope this helps.
Does any of the code I posted work?
It works up until the PlanID loop.
I think I see what the issue may be.
I think colState should look at column L to column AIPlease Login or Register to view this content.
And I had my rState as
Please Login or Register to view this content.
Should colStates actually look at the column with the state which is column C?
Colums L to AI appear to hold data for multiple plans.
Could you mock up how the template should look after the data for a state has been transferred to it?
PS That only needs to be a few lines, just enough so we get a clear picture of what you are after.
The sample template is in the same file with the others
It doesn't seem like the code is recognizing that there is a value for PlanID, ChildRate, or AdultRate
If this help, this was what worked for me when I did it one state at a time,
Please Login or Register to view this content.
The code is getting the values, but it's probably not putting them where you want to.
Now I'm not sure where the data is meant to go but have you tried stepping through this code to see what's actually happening?
By the way, why do you have this?Please Login or Register to view this content.
The first line puts dbAdultRate in 45 cells in column E.Please Login or Register to view this content.
Also, this would mean that the next lot of data would go 40 odd rows below the previous data, with a big blank space inbetween.
I say would but this,Please Login or Register to view this content.
will cause the data to always go in row 14.Please Login or Register to view this content.
PS Are the effective and expiration dates always the same for a state? Or could they change for each company/plan?
I shortened the sample version
But this is necessary because it needs to fill out the rates for ages 0-65.Please Login or Register to view this content.
This is so that the code starts filling in data from row 14Please Login or Register to view this content.
The code doesn't fill out rates, it just leaves 45 blank rows which are eventually deleted here.
Please Login or Register to view this content.
That's just very odd...Because it worked before..
This any better?
By the way, I've stepped through the code and the PlanID etc are being picked up but I can't tell if they are being put in the correct place in the template.Please Login or Register to view this content.
Omg that's great! The PlanIDs are where theyre supposed to be! (No clue what changed lol but very lovely!)
So now that that's all good..is there a way to make it so that it'll add on the data for the other rStates without overwriting what it already populated? Because right now when it loops to the next row, it resets back to
Please Login or Register to view this content.
Perhaps move it to the top of the code,
and also reset intRowKnt to 14, ready for the next set of data, after the template has been saved and reopened.Please Login or Register to view this content.
Please Login or Register to view this content.
Ah it works!..But it didnt recognize when the state changed..
Did you change anything?
I just movedto the topPlease Login or Register to view this content.
Please Login or Register to view this content.
Last edited by LLL0422; 06-05-2014 at 12:37 PM.
It think I know what the problem is, here the offset should be +1, not -1.
If you use -1 then you are comparing rstate to the value in the row above, not the value in the row below.Please Login or Register to view this content.
PS Did you also add intRowKnt where I suggested?
Yes, perfect!
Also, is there a way I can add a condition in then intPlan loop?
Please Login or Register to view this content.
That is why I have
Please Login or Register to view this content.
Last edited by LLL0422; 06-05-2014 at 01:04 PM.
If you mean if strPlanID is blank then perhaps something like this.
PS What do you want to happen to intRowKnt if there is a blank?Please Login or Register to view this content.
If there is a blank then there should be now rows allocated toward it in the template. So ideally, it would continue with the next intRowKnt for the next available plan.
So you would want to increment intRowKnt by 1 if there's a blank?
Please Login or Register to view this content.
If its blank, then the intRowKnt should be assigned to the next non-blank plan, if that makes sense.
Not really as the intRowKnt isn't assigned to a plan, it's used to indicate which row the data is to go to in the template.
When I try to run the whole macro, I get runtime error "Method of Intersect of object_Global failed" when it hits this section:
Please Login or Register to view this content.
Like if the plan is blank, then nothing should happen, and intRowKnt would just proceed for the next plans that are available
So, where on the template sheet would the next plan go?
Below the last non-blank plan?
PS There's no Intersect in that code, or anywhere else, do you have any other code in any of the open workbooks?Please Login or Register to view this content.
Yes, below the last non-blank plan.
I believe the template that I'm using may have its own macro, but I have no access to that part of the file. Is there any way around it?
If the code in the template is event code, eg for a worksheet change event, you could turn off events at the start of your code and turn them back on at the end.
Please Login or Register to view this content.
Where do I add this, at the beginning or the end?
I also have other macros in the same worksheet.
did not workPlease Login or Register to view this content.
Okay I think it works, but it says the template needs to be unprotected. Should it work after I unprotect it?
Sorry, is there a way to make it populate a protected worksheet? I do not have access to the password.
Last edited by LLL0422; 06-05-2014 at 01:56 PM.
Where did you add this code and how did it not work?
As for the template being protected, how are you expected to do anything with it, especially if you don't have the password?Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks