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.

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,

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

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

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