PivotItems in a PivotTable position -- Skip if PivotItem errors

Hello!

I have a pivot table which I have automated from a data set. There is only one field in the “row labels” and one field in the “∑ values” field. The following code works perfectly every time. However, I need to make the code dynamic in case there is an instance where one of the PivotItems isn’t contained in the data set. For instance, if there is no “BD2” in the dataset, then the following code will error out. What I need is if the code errors out since there is not a “PivotItem” contained in the dataset, then skip that line of code and continue to the next. This would be a HUGE help! Thank you so much!

Please Login or Register  to view this content.

Can anyone provide any insight into this?

Hey JP,

Actually, there is a formula I am using to calculate the BD#. BD = Business Day. The maximum number of business days is 23. Sometimes, the business days won't be in the data set. It could include BD1, BD8, BD15, BD23. It will always be completely random as well as out of order. I would like to use code for this procedure. But only want to use error handling for this specific task.

Ah, I see what you're saying. Yeah I can do a copy pastespecial values to eliminate the formula, then add a loop and replace the BD1-BD9 to include a 0 before this. I'll let you know if this indeed works.

This is the following formula that is calculating the "Business Day" taking into account the following date format:

MM/DD/YYYY:
10/5/2012
9/18/2012
8/16/2012


=IF(WEEKDAY(TRIM(B2),2)>5,"WEEKEND","BD" & NETWORKDAYS(EOMONTH(TRIM(B2),-1)+1,TRIM(B2)))


I did just add some code that does the following:

Please Login or Register  to view this content.

The code works well, but if I can include a text section within the formula to eliminate this piece of code -- that would be CRUCIAL!!

Works PERFECTLY JP! Thanks! I can now take out that whole loop procedure from the code out. AWESOME.

Reputation points well deserved