Macro for left fill in dat

[Kevw1]

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"]

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.

 

[Kevw1]

Hi so this is working for the last Milestone "End" now, but the first Milestone it is currently still pasting to the left sort of... it is actually the second Milestone in the sequence is not pasting left but all others are.

I see you have a bit of code with ""0 "" was that to select the first Milestone "0 -MDT"? I have tried to change that to ""Start"" as this is the first Milestone, but the fill still keeps going left.

 

[Kevw1]

Hi so the fill is stopping correctly at the last milestone End now thanks.

Just having a little problem with the first Milestone, the code is stopping left fill on the second Milestone but then starts filling left with the first one again.

I see you have a little code ""0 "" is that identifying the first Milestone as in 0 -MDT? I have tried changing that to ""Start" but still not stopping fill to the left on the last Milestone.

Would you mind explaining the formula a little, as to what it is doing, mainly the If statement section

 

[Fluff]

I cannot find anything with start in your sheet

 

[Kevw1]

Hi, I have updated the sheet with a bit more sample data under the milestone "start" in the tab "Macro" in column BX, rows 2-8. Hope this helps not sure if i needed to update the link to the file so have added the link again just in case. Thanks

https://www.dropbox.com/s/kgr948uzp8cxox3/Resource Forecast.xlsx?dl=0

 

[Fluff]

Ok, try this

Sub FillBlanks()

   With Sheets("Macro").UsedRange.Offset(1)
      .Value = .Value
      .SpecialCells(xlBlanks).FormulaR1C1 = "=if(rc[1]="""","""",if(rc[1]=""start"","""",rc[1]))"
      .Value = .Value
   End With
End Sub

The best way to understand the formula, is to step through the code using F8, then when the second

.Value=.Value

line is highlighted have a look at one of the cells in the macro sheet.
If you still need help understanding it, let me know

 

[Kevw1]

Hi Thanks, all looks like it is working now I have to pop out now but will have a look at the F8 next week when back on this. Many thanks for all your help with this.

 

[Fluff]

Glad to help & thanks for the feedback.

 

[Kevw1]

Hi Fluff, all looking good this morning, with one little exception. 2 of the data rows do not have a start date yet so when filling left the URN is been pasted over. Is it possible to limit the left fill, so it does not populate column A as this holds the URN for the data? thanks

 

[Kevw1]

Hi Fluff, after a bit of playing around with the data, looks like it was a mistake my side. I found the population of column A was from some old code. So just to confirm your code is working as expected and needed thanks again.