indent text based on value in previous column 2

[Cindy H]

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

 

[Cindy H]

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);
};

 

[IRstuff]

The Macro Recorder will get you the command to do the actually indenting.

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert!

https://www.youtube.com/watch?v=BKorP55Aqvg

faq731-376 forum1529 Entire Forum list

http://www.eng-tips.com/forumlist.cfm

 

[Cindy H]

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

 

[Agent666]

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

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

 

[GregLocock]

You don't even have to do it in VBA

A1=5 A2="the text" A3=rept(" ",A1)&A2

Cheers

Greg Locock


New here? Try reading these, they might help FAQ731-376

http://eng-tips.com/market.cfm?

 

[Cindy H]

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

 

[IRstuff]

No, that's what you would use VBA to do. However, if this is a one-off, you can simply delete the original column afterwards

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert!

https://www.youtube.com/watch?v=BKorP55Aqvg

faq731-376 forum1529 Entire Forum list

http://www.eng-tips.com/forumlist.cfm

 

[Cindy H]

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?

 

[IRstuff]

You could just define a new range and replace the existing range with it.

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert!

https://www.youtube.com/watch?v=BKorP55Aqvg

faq731-376 forum1529 Entire Forum list

http://www.eng-tips.com/forumlist.cfm

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Agent666]

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

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