VBA to auto fill cells based on milestones and phases

K

Kevw1

New Member
Joined
Dec 6, 2017
Messages
36
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]

​
 
Sort by date Sort by votes
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.
 
Upvote 0

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Assign Month number 1 to n based on sequential week range
Replies
6
Views
468
amolvijay
A
B
  • Question Question
Suggestions for Building a Historical Cost Database by Project
Replies
0
Views
56
babyjwhale
B
Deepakchoudhary
  • Solved
RANK based on selling multiple items in different criteria (Excel 2016)
Replies
3
Views
147
hagia_sofia
hagia_sofia
V
  • Question Question
Filter or any other function for filter the data
Replies
8
Views
176
Fuji
F
C
  • Question Question
Automate a data table into a Gannt Chart
Replies
1
Views
489
Cantrecallmyusername
C

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top