Indent based on character length

[Scott]

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]

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 here:
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]

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]

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]

This is EXACTLY what I was looking for!

Thanks for your time and expertise, it's greatly appreciated!