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
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
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
TTFN (ta ta for now)
I can do absolutely anything. I'm an expert! https://www.youtube.com/watch?v=BKorP55Aqvg
FAQ731-376: Eng-Tips.com Forum Policies forum1529: Translation Assistance for Engineers Entire Forum list http://www.eng-tips.com/forumlist.cfm
RE: indent text based on value in previous column
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
CODE --> VBA
RE: indent text based on value in previous column
A1=5 A2="the text" A3=rept(" ",A1)&A2
Cheers
Greg Locock
New here? Try reading these, they might help FAQ731-376: Eng-Tips.com Forum Policies http://eng-tips.com/market.cfm?
RE: indent text based on value in previous column
RE: indent text based on value in previous column
TTFN (ta ta for now)
I can do absolutely anything. I'm an expert! https://www.youtube.com/watch?v=BKorP55Aqvg
FAQ731-376: Eng-Tips.com Forum Policies forum1529: Translation Assistance for Engineers Entire Forum list http://www.eng-tips.com/forumlist.cfm
RE: indent text based on value in previous column
RE: indent text based on value in previous column
TTFN (ta ta for now)
I can do absolutely anything. I'm an expert! https://www.youtube.com/watch?v=BKorP55Aqvg
FAQ731-376: Eng-Tips.com Forum Policies forum1529: Translation Assistance for Engineers Entire Forum list http://www.eng-tips.com/forumlist.cfm
RE: indent text based on value in previous column
CODE
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: indent text based on value in previous column
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