Subscript out of range error & code not working

Well, I tried posting the code in the actual worksheet that it was pulling data from and no luck. So what I did is I attached another workbook with actual data.

The first sheet called Emergent Tasks is what the button on the second sheet (ETB Graph Data) gets values to add or subtract from the values in columns M thru U.

To better understand, I will give an example:

Lets say we are looking at 6/1/11 on the Emergent Tasks sheet. On that day, MMD, RP and IMD all had emergent tasks. MMD had two emergent activities: one for .020 and another for .036 for a total of .056. RP had one activity that was .021 and IMD had one activity that was .010.

So what is supposed to happen is when the user goes to the ETB Graph -Data sheet and presses the "Emergent On/Off" button, on 6/1/11 on that same sheet, in the "actual" section (columns M-U), MMD's value of .067, RP's value of .038 and IMD's value of .018 should add or subtract .056, .021 and .018.

I believe by default, the values listed on ETB Graph -Data for each department INCLUDE emergent tasks so when the user clicks the button once, it should subtract the emergent value. Thus, MMD's value of .067 should actually be .011 (.067-.056 = .011), RP should be .017 (.038-.021=.017) and IMD's should be .008 (.018-.010=.008). Make sense?

Now, why ISN'T this happening?

HP RodNuclear

The values are not supposed to match.

I'm sorry I probably made it confusing but the values listed on the Emergent Tasks are already included into the values on the ETB Graph Data sheet. To better explain this, here is the background:

These departments all have jobs that they are assigned to do within a given month. At the beginning of the month, they present that data to our department (RP) and thus that is their "Estimated" value for a day, week, month for ALL their jobs activities. It'd kinda be like you telling me, "Hey, for food on June 1st, 2011 I will be needing needing bread, water, and meat (the activities) costing me 1.00 for bread, .50 for water and 2.00 for meat for a total of 3.50" We'd then put 3.50 in the June 1st, 2011 slot for your department. However, at the last minute on June 1st, you have 2 extra people over for dinner and will be needing more food. This would be considered an 'emergent task' and so you would ask for maybe 1.00 more to cover that. We'd then input that value in the June 1st slot in the "emergent task" sheet for your department.

To make it even more confusing (but really is simple), let's say that after June 1st has passed, 1 of your 2 guests bailed on the dinner reservation, and so instead of spending the 4.50 total (3.50 for estimated and the 1.00 for emergent), you only spent 2.50 (which includes the 1.00 you spent on emergent). This value would go in the ETB Graph Data for June 1, 2011 in the "Actuals" cell for your department.

What my code should do is when you look at June 1, 2011 for your department in the "Actuals" cell, it will have 2.50, but when you press the button, it should subtract the 1.00 from emergent activity to give you 1.50. But this is supposed to be toggled i.e. if you press the button again, it will add the 1.00 back in.

The dummy example from the previous post that was solved did this beautifully and I thought I transferred relevant variables in the code to the actual workbook, but apparently I did not. The current example workbook I gave doesn't give an error when the button is pressed, but it just seems to not be working.

Hopefully this will make it more clear.

HP

So anybody got any ideas?

RodNuclear,

Updated macro code for the Representative Example. Give the following a try:

Please Login or Register  to view this content.

Hope that helps,
~tigeravatar

Oh my goodness...what did you Tiger lol? Works great now...I really need to learn this stuff. Thanks so much.

Actually, I spoke too soon. I don't know what's going on. It was working great initially and I didn't change anything, but now it seems to only work on the departments that I used in my example and not on the other departments. So sorry about this TigerAvatar, but maybe I'm doing something wrong on my end...

HP RodNuclear,

The macro creates an array of the departments and looks for them in the datasheet with matching dates, then updates values. If items are hidden in the datasheet, it won't update hidden items.

If that doesn't helps, can you post a sample workbook that is experiencing the issue? Also, the requirements the macro is meeting may be wrong. If you posted a sample workbook with a before and after, I could adjust as necessary

~tigeravatar

Thanks Tiger.

I will give it a shot but I also realize WHY it wasn't working now....it was b/c of filters! The workbook saves in whatever configuration the last user had it in and if somebody changed the filters from the Emergent Data to not fit the criteria the code is looking for (i.e. change the dates to be something NOT in the current month or change the department to be something with no emergent data and/or not listed) then the code was 'working' but to me it wasn't b/c I didn't see anything change. I realized this after Tiger Avatar did it again and it worked then it stopped working.

Thanks again to EVERYONE who offered some sort of solution.