Inserting new rows with the string above

[m_robins]

Hi,

I'm struggling to write some code in VBA that would allow me to do breakdown a list of activities. Each activity has an associated start and finish date. If the activity is longer than one day I would like to break it down into daily sub activities by inserting new rows directly below that particular activity. If the activity is only one day then evidently it would be bypassed.

Example;

If the first activity is "create steel structure" and has a start date of 01/08/2012 and a finish date of 07/08/2012, then I would like to insert 7 rows directly below that read "create steel structure 1/7", "create steel structure 2/7" and so on with respective daily dates from 01/08/2012 through to 07/08/2012. The program would then move on to the next item and analyse whether it needed to be broken down into daily activities.

The activity lies in the first column, the start dates in the second and third respectively.

Any help you can provide would be greatly appreciated!

Thanks!

[rylo]

Hi

Here goes.

Please Login or Register  to view this content.

rylo

[m_robins]

Rylo,

That's great thanks so much. Just one thing;

- For each sub activity, how can I get each string to state the progress at the end eg "create steel structure 1/7", "create steel structure 2/7", "create steel structure 3/7"?

Again thanks for your help!

[rylo]

Hi

Oops, sorry. Forgot about that bit.

Please Login or Register  to view this content.

rylo

[m_robins]

Hi again!

Thanks so much for your help before, but the code revealed a problem that I didn't previously envisage. When it breaks down the activities, it does so over non - working days. Ideally I would like the daily break down to exclude non - working days. I was thinking this could be achieved by having an input box for the dates which the vba code should exclude for the breakdown.

For example if there was an activity due to start on a Monday and finish at the end of the next Monday, then how could I get this to read as a 6 day activity instead of 8 in order to exclude the weekends, such that the activity went 1/6 for monday, 2/6 for tuesday....5/6 for friday and 6/6 for monday. Obviously this has an impact on the dates as well!

If anyone could be willing to help or at least point me in the right direction then it would be much appreciated.

Many thanks!

[rylo]

Hi

Make sure your workbook has a sheet2, select sheet1 and try this

Please Login or Register  to view this content.

This should (famous last words!!!) ignore Sat and Sun from the results.

rylo

[m_robins]

Thanks again Rylo! Is there a way to choose the dates i.e using an input box to mitigate days off? For example if there was a midweek holiday on a thursday, then to omit this day too? Obviously the holidays would change week to week so I need to find a way of tailoring it so that when I run the code then I can choose the dates that would be holidays and the code doesnt include these in the task breakdown? This is my code below for the program I am running:

Please Login or Register  to view this content.

Thanks Again!

Marc

[rylo]

Marc

Maybe you could include any extra dates on the same row as the data, but starting in column D. That way they could be tailored for each item. What I'm trying to cover off here is the possibility that there may be a task(s) that for some reason are extremely urgent, and they are done over any mid week holiday. That also leads to the possibility of working on weekends as well, so it this a possibility? Or perhaps a task is to be of lower priority and so not actioned on a particular day so staff can be directed to higher priority items?

rylo

rylo

[m_robins]

Dear Rylo,

I work in construction and we are trying to break down schedules that may span over more than one week. We are trying to capture information on a daily basis so these schedules have been broken down (thanks for your help on this) but generally in Hong Kong construction hours include saturdays for a 6 day working week. I just wanted to know if there was a way of using the InputBox function or some other means to input dates that were holidays as well to be excluded from the breakdown (as obviously the tasks wont be completed during days off work). If this is impractical then perhaps you could help me in amending the formula to simply exclude Sundays?

Many thanks,

Marc

[rylo]

Marc

If you just want to exclude Sundays, then do something like

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

As you may have a long time span, then I don't see that trying to get what could be a long list of holidays from an input box would be practical. If you don't want to put them against every item (I can see how that would be onerous), how about putting a list of them on a separate sheet in the workbook. That way they could be centrally updated, and all items could be compared with that listing. Would that be an option?

rylo

[m_robins]

Rylo, that could work! So would this reference each activity against those dates and then exclude them in the breakdown if the holidays would fall within that time frame? If so that would be great! I could include this in the same worksheet also if possible.

Thanks! Marc

[rylo]

Hi

Create a new sheet in the workbook, name it Holidays and enter in 2 dates (30/8/2012 and 11/9/2012).

Now on sheet 1, enter the following details in A2:C2 Create Steel Structure | 15/8/2012 | 30/9/2012

Then try

Please Login or Register  to view this content.

This should ignore Sundays, and the 2 nominated dates.

rylo

[m_robins]

Rylo,

I see to get an error in the third line of code that reads run-time error 9: Subscript out of range. When I select debug it highlights:

Set OutSh = Sheets("Sheet2")

Should this not read something else instead of sheet 2? ie Holidays?

Thanks,

Marc

[rylo]

Marc

Sheet2 has been nominated in my code as the output sheet. There has to be a sheet of this name in the workbook. See post #6.

rylo

[m_robins]

Hi Rylo,

I've run the program and there's just one small problem! If I had a list of 10 activities then the break down will happen after all 10 activities. Is there a way to break them down in sequence such that Activity 1 breaks down, then 2, then 3, without effectively duplicating the activity under the original list? The original program did this and ideally I would quite like to keep this feature!


Thanks again,
Marc

[rylo]

Marc

It does break down the first entry, then the second....

If it isn't doing that for you, then put up an example workbook.

rylo