Indent cell by a variable

biske

New Member
Joined
Oct 27, 2006
Messages
4
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
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,912
Office Version
  1. 365
Platform
  1. Windows
Brian

How exactly do you want to indent?
 

biske

New Member
Joined
Oct 27, 2006
Messages
4
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

Board Regular
Joined
Jul 25, 2006
Messages
169
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

Code:
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

New Member
Joined
Oct 27, 2006
Messages
4

ADVERTISEMENT

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

Board Regular
Joined
Jul 25, 2006
Messages
169
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

New Member
Joined
Oct 27, 2006
Messages
4
Thanks!!

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

Thanks for helping a newbie.

Brian
 

Watch MrExcel Video

Forum statistics

Threads
1,113,795
Messages
5,544,337
Members
410,603
Latest member
rseckler
Top