Indent cell by a variable

[biske]

I have a bill of material exported from a business system. Column A contains the Level of the item in the BOM, and Column B and C have the part number and description.

I want to indent the part number and description by the level number. In other words, I want to Indent column B by the value in column A.

Is this possible, with and without VBA (which I am unfamiliar with)?

Thanks.

Brian

 

[Norie]

Brian

How exactly do you want to indent?

 

[biske]

I want to indent within the cell that the text is in.

For example, if the item is at level 2 (2 in column A), I want to double indent the text in the description.

 

[Bob Rooney]

I don't think that this can be done without VBA, except for the manual indent feature.

Here is a VBA option. Pre-select your range and run the following

Sub TEST()
Dim Cell As Range

On Error Resume Next
    For Each Cell In Selection
        With Cell
            .IndentLevel = False  '' clears any existing indents
            .InsertIndent .Offset(0, -1).Value
        End With
    Next Cell
On Error GoTo 0

End Sub

 

[biske]

This may seem ridiculous, but I have been using Excel for a long time, and used some of the more advanced formulas, but have never used or run VBA.

Could you explain to me how to 'run' this?

thanks

 

[Bob Rooney]

On your first time, only have the workbook in question open. Close all other open workbooks.

From Excel, go into the Visual Basic Editor (VBE) by one of the following ways:

1. Tools>Macro>Visual Basic Editor
2. Alt+F11 key combination

Once there, hit Ctrl+R to ensure that the Project Explorer is visible. Wherever it is docked (usually on the left side), you will see a reference to your workbook. If you expand the options, you should only see a folder called Microsoft Excel Objects. Expanding further, you should see the names of the various sheets in your workbook. Each of those sheets can hold macros, but that is for later.

From the top menu, Insert > Module. In the Project Explorer, Excel will have added an additional folder called Modules. On the main part of the screen, you should see a blank sheet (or else Option Explicit), which is the code sheet (it will be called Module1 in the Project Explorer).

Copy the code for the routine into that blank section of Module1.

Close the VBE (Alt+Q) to go back to Excel.

Select the range of items in column B

Tools>Macro>Macros (shortcut key combo is Alt+F8) will bring up a dialog box. The only macro you have should be highlighted. Choose Run and the macro will process.

A lot of steps at first, but these will become second nature in no time.

 

[biske]

Thanks!!

That's awsome. This will same a ton of keystrokes and editing.

Thanks for helping a newbie.

Brian