Posted to microsoft.public.excel.misc
|
|
Indent based on character length
This is EXACTLY what I was looking for!
Thanks for your time and expertise, it's greatly appreciated!
"Dave Peterson" wrote:
The A's are indented like this:
String length Indent
-------- ------ ------
A 1 0
AA 2 1
AAA 3 2
AAAA 4 3
So each indent is one less than the length of the string.
The 1's are indented like this:
String length Indent
-------- ------ ------
1 1 0
1.1 3 1
1.1.1 5 2
So does this mean that dots should be ignored in the string?
A.A.A would be indented 2 (since its length ignoring the dots is 3).
So the question is a lot different <vbg:
Option Explicit
Sub TestMe()
Dim myRng As Range
Dim myStr As String
Dim myCell As Range
Dim myIndentLevel
With Worksheets("Sheet1")
Set myRng = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))
End With
For Each myCell In myRng.Cells
myStr = myCell.Value
myIndentLevel = Len(Replace(myStr, ".", "")) - 1
If myIndentLevel 15 Then
'change it to 15???
myIndentLevel = 15
End If
If myIndentLevel 0 Then
myCell.IndentLevel = myIndentLevel
End If
Next myCell
End Sub
This requires xl2k or higher (Replace was added in xl2k).
If you're using xl97, you can change this line:
myIndentLevel = Len(Replace(myStr, ".", "")) - 1
to
myIndentLevel = Len(application.substitute(myStr, ".", "")) - 1
Scott wrote:
Dave,
Thanks for your suggestions!
It appears that I may have mistated my intended need. Basically, what I need
the subroutine/macro to do is check the character length in A1:A6500. If
there is a value, then check for the length. If the length is greater than 1,
then indent based on the character length.
pseudocode:
Does a value for Cell:A1 exist?
If exists, check the length of the character
If the character length is 1, indent based on that value.
For example, the output would be:
A
AA
AAA
AAAA
or
1
1.1
1.1.1
Thanks again for your help!
"Dave Peterson" wrote:
Maybe...
Option Explicit
Sub TestMe()
Dim myRng As Range
Dim myCell As Range
Dim myIndentLevel
With Worksheets("Sheet1")
Set myRng = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))
End With
For Each myCell In myRng.Cells
myIndentLevel = Len(myCell.Value) - 1
If myIndentLevel 15 Then
'change it to 15???
myIndentLevel = 15
End If
myCell.IndentLevel = myIndentLevel
Next myCell
End Sub
If you're new to macros:
Debra Dalgleish has some notes how to implement macros he
http://www.contextures.com/xlvba01.html
David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm
(General, Regular and Standard modules all describe the same thing.)
Scott wrote:
Does anyone know how to perform an indent based on the amount of characters
in the first cell.
In example: (not specific to 1, 10, 100)
If cell contains "1" (or any other one charachter length) then there is no
indent
If a cell contains "10" (or any other two length characters) then there is
one level of indentation
If a cell contains "100" (or any other three char length) then there is two
levels of indentation
.........
Thanks in advance.
--
Dave Peterson
--
Dave Peterson
|