Hi can anyone help please?
I am trying to create a macro to auto fill cells to the left, based on another cells population. I have a macro that fills to the right, but can not get it to fill to the left, still relatively new to VBA.
I am creating a forecast for resources, the only known value is the milestone the project has reached. I have a table with Project in the rows and Month in the columns. The table then has the Milestone identified against the Project and month delivered e.g.
[TABLE="width: 474"]
<colgroup><col span="6"></colgroup><tbody>[TR]
[TD] [/TD]
[TD]Jan-18[/TD]
[TD]Feb-18[/TD]
[TD]Mar-18[/TD]
[TD]Apr-18[/TD]
[TD]May-18[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Milestone 3[/TD]
[/TR]
[TR]
[TD]Project 2[/TD]
[TD] [/TD]
[TD]Milestone 1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Milestone 2[/TD]
[/TR]
[TR]
[TD]Project 3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Milestone 2[/TD]
[TD] [/TD]
[TD]Milestone 5[/TD]
[/TR]
</tbody>[/TABLE]
There is a changing number of projects but will be around 100-200 and this will span multiple years 10+, hence needs to keep running until it has got to the end of the table.
I currently have a table with all this data in on a tab called "Monthly View" and this data is pasted in to a new tab called "Macro" in excel which is where the Macro runs.
I need to be able to use the populated Milestone and fill the cells to the Left, until it hits the next Milestone. When the fill reaches the first Milestone "Project Start" it must stop likewise when it sees the last milestone "Project Finish" there will be no further Milestones. Not all milestones will be available so will have to account for this also. e.g.
[TABLE="width: 474"]
<tbody style="background-attachment: scroll; background-clip: border-box; background-color: transparent; background-image: none; background-origin: padding-box; background-position-x: 0%; background-position-y: 0%; background-repeat: repeat; background-size: auto; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-size-adjust: none; font-stretch: normal; font-style: normal; font-variant: normal; font-weight: 400; line-height: normal; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">[TR="bgcolor: transparent"]
[TD][/TD]
[TD]Jan-18[/TD]
[TD]Feb-18[/TD]
[TD]Mar-18[/TD]
[TD]Apr-18[/TD]
[TD]May-18[/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD]Project 1[/TD]
[TD="bgcolor: transparent"]
[TD="bgcolor: transparent"]
[TD="bgcolor: transparent"]
[TD="bgcolor: transparent"]
[TD="bgcolor: transparent"]Milestone 3[/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD]Project 2[/TD]
[TD="bgcolor: transparent"]
[TD="bgcolor: transparent"]Milestone 1[/TD]
[TD="bgcolor: transparent"]
[TD="bgcolor: transparent"]
[TD="bgcolor: transparent"]Milestone 2[/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD]Project 3[/TD]
[TD="bgcolor: transparent"]
[TD="bgcolor: transparent"]
[TD="bgcolor: transparent"]Milestone 2[/TD]
[TD="bgcolor: transparent"]
[TD="bgcolor: transparent"]Milestone 5[/TD]
[/TR]
</tbody>[/TABLE]
Thanks for any help that can be provided.
I am trying to create a macro to auto fill cells to the left, based on another cells population. I have a macro that fills to the right, but can not get it to fill to the left, still relatively new to VBA.
I am creating a forecast for resources, the only known value is the milestone the project has reached. I have a table with Project in the rows and Month in the columns. The table then has the Milestone identified against the Project and month delivered e.g.
[TABLE="width: 474"]
<colgroup><col span="6"></colgroup><tbody>[TR]
[TD] [/TD]
[TD]Jan-18[/TD]
[TD]Feb-18[/TD]
[TD]Mar-18[/TD]
[TD]Apr-18[/TD]
[TD]May-18[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Milestone 3[/TD]
[/TR]
[TR]
[TD]Project 2[/TD]
[TD] [/TD]
[TD]Milestone 1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Milestone 2[/TD]
[/TR]
[TR]
[TD]Project 3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Milestone 2[/TD]
[TD] [/TD]
[TD]Milestone 5[/TD]
[/TR]
</tbody>[/TABLE]
There is a changing number of projects but will be around 100-200 and this will span multiple years 10+, hence needs to keep running until it has got to the end of the table.
I currently have a table with all this data in on a tab called "Monthly View" and this data is pasted in to a new tab called "Macro" in excel which is where the Macro runs.
I need to be able to use the populated Milestone and fill the cells to the Left, until it hits the next Milestone. When the fill reaches the first Milestone "Project Start" it must stop likewise when it sees the last milestone "Project Finish" there will be no further Milestones. Not all milestones will be available so will have to account for this also. e.g.
[TABLE="width: 474"]
<tbody style="background-attachment: scroll; background-clip: border-box; background-color: transparent; background-image: none; background-origin: padding-box; background-position-x: 0%; background-position-y: 0%; background-repeat: repeat; background-size: auto; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-size-adjust: none; font-stretch: normal; font-style: normal; font-variant: normal; font-weight: 400; line-height: normal; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">[TR="bgcolor: transparent"]
[TD][/TD]
[TD]Jan-18[/TD]
[TD]Feb-18[/TD]
[TD]Mar-18[/TD]
[TD]Apr-18[/TD]
[TD]May-18[/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD]Project 1[/TD]
[TD="bgcolor: transparent"]
Milestone 3
[/TD][TD="bgcolor: transparent"]
Milestone 3
[/TD][TD="bgcolor: transparent"]
Milestone 3
[/TD][TD="bgcolor: transparent"]
Milestone 3
[/TD][TD="bgcolor: transparent"]Milestone 3[/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD]Project 2[/TD]
[TD="bgcolor: transparent"]
Milestone 1
[/TD][TD="bgcolor: transparent"]Milestone 1[/TD]
[TD="bgcolor: transparent"]
Milestone 2
[/TD][TD="bgcolor: transparent"]
Milestone 2
[/TD][TD="bgcolor: transparent"]Milestone 2[/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD]Project 3[/TD]
[TD="bgcolor: transparent"]
Milestone 2
[/TD][TD="bgcolor: transparent"]
Milestone 2
[/TD][TD="bgcolor: transparent"]Milestone 2[/TD]
[TD="bgcolor: transparent"]
Milestone 5
[/TD][TD="bgcolor: transparent"]Milestone 5[/TD]
[/TR]
</tbody>[/TABLE]
Thanks for any help that can be provided.