Filling in a tree

alphiealphie

New Member
Joined
Jul 15, 2013
Messages
3
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

spw6WDK.png


HbQy8xO.png


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
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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>
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

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