×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • 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.

Students Click Here

indent text based on value in previous column
2

thread770-458349

indent text based on value in previous column

indent text based on value in previous column

(OP)
similar to this thread - https://www.eng-tips.com/viewthread.cfm?qid=225584

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....

Thank you in advance!!

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

var ss = SpreadsheetApp.getActiveSpreadsheet();

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 sheet = SpreadsheetApp.getActiveSpreadsheet();

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

}];
sheet.addMenu("Indent Text", entries);
};

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login

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
Design for Additive Manufacturing (DfAM)
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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close