Filling in a tree

[alphiealphie]

I am attempting to use Excel to fill in the blank cells in an organisational hierarchy I have. Before and After scenarios below.
I have attempted pseudocode on this.

For all rows
For all cells in row
If cell is populated, row++
If cell is not populated, copy the value immediately above to the current cell and check next cell in row

Here are my ideal before/after shots

So far i have this but it's not achiving the right result - cell C10 should not populate in my example. I would love some help!

Sub FillTree()
Dim rng as Range
Dim r as Range
Dim c as Range
Dim cl as Range

Set rng = Range("A1:E50") '## Modify this to the full range you want to fill in.

For Each c In rng.Columns '# iterate over each column
For Each cl in c.Cells '# check each cell in the column
If Trim(cl) = vbNullString Then '# if the cell is empty, fill from above
cl = cl.Offset(-1,0)
Else: '# do nothing if the cell is not empty
End If
Next
Next

End Sub

 

[sijpie]

One of the problems you have is that you are going through each cell twice:
For each c in rng.columns and again in
for each cl in c.cells

The following macro runs through each column only once. It processes the columns from right to left as it will check to see if the cell to the left is empty before it puts in the formula.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> FillOrgTree()<br>    <SPAN style="color:#00007F">Dim</SPAN> rC <SPAN style="color:#00007F">As</SPAN> Range, rIn <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> lR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, lC <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">Set</SPAN> rIn = Application.InputBox("Select range to get filled in", _<br>                Title:="Fill organisation hierarchy", Type:=8)<br>    <SPAN style="color:#00007F">Set</SPAN> rC = rIn.Cells(1, 1) <SPAN style="color:#007F00">' set rC to top left cell of range</SPAN><br>    <br>    <SPAN style="color:#007F00">'Process the columns from right to left</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> lC = rIn.Columns.Count - 1 <SPAN style="color:#00007F">To</SPAN> 0 <SPAN style="color:#00007F">Step</SPAN> -1<br>        <SPAN style="color:#00007F">For</SPAN> lR = 0 <SPAN style="color:#00007F">To</SPAN> rIn.Rows.Count - 1<br>            <SPAN style="color:#00007F">If</SPAN> rC.Offset(lR, lC) = vbNullString And _<br>                    rC.Offset(lR - 1, lC) <> vbNullString And _<br>                    rC.Offset(lR, lC - 1) = vbNullString <SPAN style="color:#00007F">Then</SPAN><br>                rC.Offset(lR, lC).FormulaR1C1 = "=R[-1]C"<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> lR<br>    <SPAN style="color:#00007F">Next</SPAN> lC<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[sijpie]

Forgot to say, it will ask for the range to process. This by using Application.Inputbox() with Type:=8. So you can use the mouse to select the range!