Excel:set indent level based on value of a cell
Excel:set indent level based on value of a cell
(OP)
I am able to extract an indented BOM from our PLM software for placement into Excel 2003. The tab-delimited BOM file has the indents shown via a column of values. I would like to indent certain columns in Excel based on the value in the indent level column that has been imported.
Example:
A B C
Level Part No Description
.1 0001 Part1
..2 0002 Part2
..2 0003 Part3
...3 0004 Part4
I would like to add indents in columns B & C; 1 space for a Level column cell value of .1, 2 spaces for a value of ..2, etc.
Has anyone tried anything similar?
Thanks
Example:
A B C
Level Part No Description
.1 0001 Part1
..2 0002 Part2
..2 0003 Part3
...3 0004 Part4
I would like to add indents in columns B & C; 1 space for a Level column cell value of .1, 2 spaces for a value of ..2, etc.
Has anyone tried anything similar?
Thanks





RE: Excel:set indent level based on value of a cell
I doubt you will be able to do it directly with that cell, because text alignment isn't one of the things available in Conditional Formatting. You could build up the indents with spaces in an additional column with a formula like
=REPT(" ",A2)&B2
If your indent column actually has those dots in it, then your formula would have to be
=REPT(" ",LEN(A2)-1)&B2
Of course, this formula would go in your first blank row. You would then hide the actual data rows.
-handleman, CSWP (The new, easy test)
RE: Excel:set indent level based on value of a cell
CODE
Sub AlignColumns()
Dim c As Range, a As Variant
Set c = [A2]
Do While Not IsEmpty(c.Value)
'get the value in the first column and strip the decimals
a = CVar(Replace(c.Value, ".", ""))
'you can also replace the decimal points in the first colum:
' c.Value = Replace(c.Value, ".", "")
'
'set indents of the first and second columns.
'assume that these are formatted as "Text"!
c.HorizontalAlignment = xlLeft
c.IndentLevel = a
c.Offset(0, 1).HorizontalAlignment = xlLeft
c.Offset(0, 1).IndentLevel = a
'go to the next row
Set c = c.Offset(1, 0)
Loop
Set c = Nothing
End Sub
Cheers,
Joerd
Please see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.
RE: Excel:set indent level based on value of a cell
Thanks for the code. I tried it and it works perfectly! A star for you is well deserved! :)
Cheers,
Stick1
RE: Excel:set indent level based on value of a cell
One additional question if I may...how do I assign the indents to columns other than the first and second?
Thanks,
Stick1
RE: Excel:set indent level based on value of a cell
c.Offset(0, 1).HorizontalAlignment = xlLeft
c.Offset(0, 1).IndentLevel = a
but replace the 0,1 by 0,2 and 0,3 and so forth.
The Offset(row,col) method gives you a cell that is (row) rows below the base cell c, and (col) columns to the right.
Cheers,
Joerd
Please see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.