VBA to auto fill cells based on milestones and phases

[Kevw1]

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 1
Project	Jun-17	Jul-17	Aug-17	Sep-17		Milestones	Phase
Project 1	M1			M3		M1	1
Project 2	M1	M2	M3	M4		M2	1
Project 3		M1		M2		M3	2
Project 4	M1			M5		M4	2
Project 5	M2			M3		M5	3
Project 6	M4			M5
Table 2
Project	Jun-17	Jul-17	Aug-17	Sep-17
Project 1	1	1	1	2
Project 2	1	1	2	2
Project 3		1	1	1
Project 4	1	1	1	3
Project 5	1	1	1	1
Project 6	2	2	2	3

<colgroup><col><col span="4"><col><col><col></colgroup><tbody>
</tbody>

​

 

[Kevw1]

brill all working now, many thanks for your help with this much appreciated, just one last question how do you spot the "" in the blank cells as i can not see then still.

 

[Fluff]

As you said you copy/pasted the data from the monthly view sheet, I looked at that & saw that you had formulae in there. And that in-itself told me that the cells in the test sheet weren't blank. Whilst they appear blank & =len(U2) will return 0, they aren't as shown by the isblank formula.

Glad to help & thanks for the feedback