×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Are you an
Engineering professional?
Join Eng-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# indent text based on value in previous column2

## indent text based on value in previous column

(OP)

I am trying to find VBA (not understanding how to create my own yet) that will do the following:

Automatically update the formatting in a spreadsheet to indent the text in Column D, based on the value in Column C.

I've seen so many VBA scripts and macros (from google searches) but they are not working or I'm not able to adapt them to my spreadsheet.

the values in Column C represents the "level" (1 to 5)
based on the values in Column C, I want the text in Column D to indent 3,6,9,12 spaces or 5,10,15,20 spaces (the actual number of spaces doesn't matter to me as long as it can be easily distinguished visually)

Here are some of the related solutions posted - the first one might have errors as I already started to try and modify it

Sub SetIndentLevel()
' SetIndentLevel Macro
'
' Used integer value in Column B to set the indent level in Column C
'

Dim i As Long, nRows As Long
'
nRows = ActiveSheet.Range("C1").End(xlDown).Row - 1 ' minus 1 assumes a header row
'
Application.ScreenUpdating = False
For i = 1 To nRows
ActiveSheet.Cells(i + 1, 3).IndentLevel = Application.WorksheetFunction.Max(Application.WorksheetFunction.Min(CInt(ActiveSheet.Cells(i + 1, 2)), 25), 0)
Next i
Application.ScreenUpdating = True
'
End Sub

====================
Sub AlignColumns()
Dim c As Range, a As Variant

Set c = [C3]

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

I would also love it if you can explain the code....

Cindy

### RE: indent text based on value in previous column

(OP)
and this is a script for indenting / outdenting text (manually) for google SHEETS that I had found in another forum....my question above is related to MS Excel

function moveText(direction) {
var values = ss.getActiveRange().getValues();
var cols = ss.getActiveRange().getNumColumns();
var rows = ss.getActiveRange().getNumRows();

var newValues = new Array();

for (x = 1; x <= rows; x++) {
for (y = 1; y <= cols; y++) {
var cell = ss.getActiveRange().getCell(x, y);
var value = cell.getValue();
var formula = (direction == ">>>") ? '=CONCAT(REPT( CHAR( 160 ), 5),"' + value + '")'
: '=IF(TRIM(LEFT("' + value + '", 5))=CONCAT(REPT( CHAR( 160 ), 5),""), MID("' + value + '", 6, LEN("' + value + '")), TRIM("' + value + '"))';

if (value != '') {
cell.setFormula([formula]);
cell.setValue(cell.getValue());
} else {
cell.setValue(['']);
}
}
}
};

function indentText() {
moveText(">>>");
};

function flushLeft() {
moveText("<<<");

};

function onOpen() {

var entries = [{
name : ">>>",
functionName : "indentText"
},{
name : "<<<",
functionName : "flushLeft"

}];
};

### RE: indent text based on value in previous column

(OP)
Hello IRstuff...

Just did this and the script doesn't make sense (it doesn't follow a chrono order)...and it's not based on a value set in another location so that's not quite what I am looking for. Thank you for the suggestion though.

Cindy

### RE: indent text based on value in previous column

try this, it assumes first row with the data you want to indent is row 1 on sheet1. It iterates until it hits the first empty cell in column 'D'.

#### CODE --> VBA

Sub indent()
For Each cell In Sheet1.Range("D:D")
If Not cell = "" Then
cell.IndentLevel = cell.Offset(0, -1).Value
Else
Exit Sub
End If
Next
End Sub 

### RE: indent text based on value in previous column

(OP)
Is there a way to do it without having to repeat the text? (i.e. so it formats the original text column?

### RE: indent text based on value in previous column

(OP)
Agent 666, how do I modify if the data starts on row 3, the levels are indicated in Column C and the text is in Column D?

### RE: indent text based on value in previous column

#### CODE

Sub indent()
For Each cell In Sheet1.Range(Cells(3, "D"), Cells(3, "D").End(xlDown))
cell.IndentLevel = cell.Offset(0, -1).Value
Next
End Sub 

Skip,

Just traded in my OLD subtlety...
for a NUance!

### RE: indent text based on value in previous column

#### Quote (Cindy H)

Is there a way to do it without having to repeat the text? (i.e. so it formats the original text column?
The only way would be to have another helper column that was simply hidden. Not ideal doing this though.

Note the formula posted is simply only adding a space to the beginning of the text term, it's not the same as indenting it. For example if you are doing something to the text string afterwards it's going to have spaces in front of it that might need to be stripped off. Standard indent is also visually quite a bit wider than a standard space.

This is a slight improvement on Skip's solution that will work if there are any gaps in the data (like some blank lines have been inserted):-

#### CODE --> VBA

Sub indent()
start_row = 3
txt_col = "D"
For Each cell In Sheet1.Range(Cells(start_row, txt_col), Cells(Cells(Rows.Count, txt_col).End(xlUp).Row, txt_col))
If Not cell = "" Then
cell.IndentLevel = cell.Offset(0, -1).Value
End If
Next
End Sub 

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

#### Resources

Low-Volume Rapid Injection Molding With 3D Printed Molds
Learn methods and guidelines for using stereolithography (SLA) 3D printed molds in the injection molding process to lower costs and lead time. Discover how this hybrid manufacturing process enables on-demand mold fabrication to quickly produce small batches of thermoplastic parts. Download Now
Examine how the principles of DfAM upend many of the long-standing rules around manufacturability - allowing engineers and designers to place a partâ€™s function at the center of their design considerations. Download Now
Taking Control of Engineering Documents
This ebook covers tips for creating and managing workflows, security best practices and protection of intellectual property, Cloud vs. on-premise software solutions, CAD file management, compliance, and more. Download Now

Close Box

# Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!