Hi this is my first time posting and other then recording macros still very new to VBA but keen to learn, I have the following problem if anyone can help please? I have had a look in the forums and am struggling to utilise / find suitable code.
For summary only in table 1, this is my starting data, I have milestones that are against projects and months achieved for every milestone.
There are many more projects and months that span years in total, I need to convert the milestone to the Phase and populate the phase for the blank months until the next milestone is reached. In some cases milestones will be missing and may start half way through.
What I am trying to do is create a macro that:-
1. Check the value in the cell and matches to the milestone phase
2. If the cell is blank uses the previous milestone to calculate the phase
3. Repeat for every column against each project (many months over many years)
4. Repeat for each project
5. this can overwrite the data in the original sheet or creating a copy of the original sheet and update?
I will be changing the below "projects" and "Milestones" for actual data once I can understand the structure for this.
Table 2 show the results I am expecting, thanks again for any guidance where to start from.
[TABLE="width: 508"]
<colgroup><col><col span="4"><col><col><col></colgroup><tbody>[TR]
[TD="align: center"]Table 1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Project[/TD]
[TD="align: center"]Jun-17[/TD]
[TD="align: center"]Jul-17[/TD]
[TD="align: center"]Aug-17[/TD]
[TD="align: center"]Sep-17[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Milestones[/TD]
[TD="align: center"]Phase[/TD]
[/TR]
[TR]
[TD="align: center"]Project 1[/TD]
[TD="align: center"]M1[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]M3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]M1[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]Project 2[/TD]
[TD="align: center"]M1[/TD]
[TD="align: center"]M2[/TD]
[TD="align: center"]M3[/TD]
[TD="align: center"]M4[/TD]
[TD="align: center"][/TD]
[TD="align: center"]M2[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]Project 3[/TD]
[TD="align: center"] [/TD]
[TD="align: center"]M1[/TD]
[TD="align: center"] [/TD]
[TD="align: center"]M2[/TD]
[TD="align: center"][/TD]
[TD="align: center"]M3[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]Project 4[/TD]
[TD="align: center"]M1[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]M5[/TD]
[TD="align: center"][/TD]
[TD="align: center"]M4[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]Project 5[/TD]
[TD="align: center"]M2[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]M3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]M5[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]Project 6[/TD]
[TD="align: center"]M4[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]M5[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Table 2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Project[/TD]
[TD="align: center"]Jun-17[/TD]
[TD="align: center"]Jul-17[/TD]
[TD="align: center"]Aug-17[/TD]
[TD="align: center"]Sep-17[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Project 1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Project 2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Project 3[/TD]
[TD="align: center"] [/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Project 4[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Project 5[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Project 6[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]
For summary only in table 1, this is my starting data, I have milestones that are against projects and months achieved for every milestone.
There are many more projects and months that span years in total, I need to convert the milestone to the Phase and populate the phase for the blank months until the next milestone is reached. In some cases milestones will be missing and may start half way through.
What I am trying to do is create a macro that:-
1. Check the value in the cell and matches to the milestone phase
2. If the cell is blank uses the previous milestone to calculate the phase
3. Repeat for every column against each project (many months over many years)
4. Repeat for each project
5. this can overwrite the data in the original sheet or creating a copy of the original sheet and update?
I will be changing the below "projects" and "Milestones" for actual data once I can understand the structure for this.
Table 2 show the results I am expecting, thanks again for any guidance where to start from.
[TABLE="width: 508"]
<colgroup><col><col span="4"><col><col><col></colgroup><tbody>[TR]
[TD="align: center"]Table 1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Project[/TD]
[TD="align: center"]Jun-17[/TD]
[TD="align: center"]Jul-17[/TD]
[TD="align: center"]Aug-17[/TD]
[TD="align: center"]Sep-17[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Milestones[/TD]
[TD="align: center"]Phase[/TD]
[/TR]
[TR]
[TD="align: center"]Project 1[/TD]
[TD="align: center"]M1[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]M3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]M1[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]Project 2[/TD]
[TD="align: center"]M1[/TD]
[TD="align: center"]M2[/TD]
[TD="align: center"]M3[/TD]
[TD="align: center"]M4[/TD]
[TD="align: center"][/TD]
[TD="align: center"]M2[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]Project 3[/TD]
[TD="align: center"] [/TD]
[TD="align: center"]M1[/TD]
[TD="align: center"] [/TD]
[TD="align: center"]M2[/TD]
[TD="align: center"][/TD]
[TD="align: center"]M3[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]Project 4[/TD]
[TD="align: center"]M1[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]M5[/TD]
[TD="align: center"][/TD]
[TD="align: center"]M4[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]Project 5[/TD]
[TD="align: center"]M2[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]M3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]M5[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]Project 6[/TD]
[TD="align: center"]M4[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]M5[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Table 2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Project[/TD]
[TD="align: center"]Jun-17[/TD]
[TD="align: center"]Jul-17[/TD]
[TD="align: center"]Aug-17[/TD]
[TD="align: center"]Sep-17[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Project 1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Project 2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Project 3[/TD]
[TD="align: center"] [/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Project 4[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Project 5[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Project 6[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]