How can I query the 'indent' level of text in a cell?

This is part of the format -> cell -> alignment setup.

There are many things that can be queried using an Excel function.

But how can I query the indent level?

I have tried/search for hours...please help!

Thank you.

Activecell.IndentLevel

--
Jim
"NJDevil" <NJDevil@discussions.microsoft.com> wrote in message
news:63333A4C-45F1-4B97-89AE-14FD45257E6E@microsoft.com...
> This is part of the format -> cell -> alignment setup.
>
> There are many things that can be queried using an Excel function.
>
> But how can I query the indent level?
>
> I have tried/search for hours...please help!
>
> Thank you.

Hi Jim,

Thanks for the reply. However, I don't know how to use that. It sounds
like I need to do some programming to get the info I need. Can you PLEASE
provide me with some code (or how ever you would use it) so that I may
integrate that solution into my spreadsheet? I have spend a lot of time on
this spreadheet and this is the one thing that I really need to make it do
what I need!

Once I have the code, how do I invoke it in Excel? Sorry for being sucha
newby but I'm not an Excel programmer. I really wish I could just invoke a
function, but the '=CELL' function does not let me query the indent length.

THANK YOU VERY MUCH!!

NJDevil

"Jim Rech" wrote:

> Activecell.IndentLevel
>
> --
> Jim
> "NJDevil" <NJDevil@discussions.microsoft.com> wrote in message
> news:63333A4C-45F1-4B97-89AE-14FD45257E6E@microsoft.com...
> > This is part of the format -> cell -> alignment setup.
> >
> > There are many things that can be queried using an Excel function.
> >
> > But how can I query the indent level?
> >
> > I have tried/search for hours...please help!
> >
> > Thank you.
>
>
>

Make Jim's solution into a UDF. As you're not familiar with UDFs, read
David McRitchie's "Getting Started with Macros and User Defined
Functions":


http://www.mvps.org/dmcritchie/excel/getstarted.htm


Public Function IndentLevel(Ref As Range) As Long
Application.Volatile
IndentLevel = Ref.IndentLevel
End Function


Call from the worksheet as

B1: =IndentLevel(A1)

to find the indent level of cell A1. Note that even with the
Application.Volatile command, which makes the function recalculate
whenever the worksheet is recalculated, changing the indent level
doesn't fire a worksheet calculation, so you'll need to use F9 to be
sure it's accurate after changing an indent level.

In article <952FB8FA-4144-42FD-B7AE-D597024CDF06@microsoft.com>,
"NJDevil" <NJDevil@discussions.microsoft.com> wrote:

> Thanks for the reply. However, I don't know how to use that. It sounds
> like I need to do some programming to get the info I need. Can you PLEASE
> provide me with some code (or how ever you would use it) so that I may
> integrate that solution into my spreadsheet? I have spend a lot of time on
> this spreadheet and this is the one thing that I really need to make it do
> what I need!
>
> Once I have the code, how do I invoke it in Excel? Sorry for being sucha
> newby but I'm not an Excel programmer. I really wish I could just invoke a
> function, but the '=CELL' function does not let me query the indent length.

Thanks JE. I've been reading how to define and setup a amcro from some info
on the web...

http://msdn.microsoft.com/library/de.../odc_super.asp

I think I'm on the right track.

Can you have a look at this and make some adjustments to get it to work?
Basically Im just trying to put the indent level in a separate column.
THANKS FOR YOUR HELP!

Sub query_indent()


Dim indent_length
Dim Row
Dim Col

'Not sure if I need all three Dim statements above.


'Select the active worksheet
Set wrkSheet = ActiveWorkbook.ActiveSheet

'indent_length = ActiveCell.IndentLevel

'Start on the second row since the top row is the header
'Range("C2").Value = indent_length

'Start at A2 (i.e. Row 2, Col A)
Row = 2
Col = 1
Contents = ActiveCell.Address(Row, Col).Cell.Value

'It does not like the above statement... can I use Row and Col variables?
'If not, how can I increment through it?

Do While Contents <> " "

indent_length = ActiveCell.IndentLevel

'Now place the indent length in Col C of the same row
ActiveCell.Address(Row, Col + 2).Cell.Select
ActiveCell.Value = indent_length

'Increment to the next row
Row = Row + 1

'Grab the data in the next cell (next row, same col)
ActiveCell.Address(Row, Col).Cell.Select
Loop

End Sub



I guess you can think of the above as psuedo code, but if you can help me
get it right I would certainly appreciate it!

Thank you again. It's 1;10am here (NJ) and I'm going to call it a night.

Cheers!



"JE McGimpsey" wrote:

> Make Jim's solution into a UDF. As you're not familiar with UDFs, read
> David McRitchie's "Getting Started with Macros and User Defined
> Functions":
>
>
> http://www.mvps.org/dmcritchie/excel/getstarted.htm
>
>
> Public Function IndentLevel(Ref As Range) As Long
> Application.Volatile
> IndentLevel = Ref.IndentLevel
> End Function
>
>
> Call from the worksheet as
>
> B1: =IndentLevel(A1)
>
> to find the indent level of cell A1. Note that even with the
> Application.Volatile command, which makes the function recalculate
> whenever the worksheet is recalculated, changing the indent level
> doesn't fire a worksheet calculation, so you'll need to use F9 to be
> sure it's accurate after changing an indent level.
>
> In article <952FB8FA-4144-42FD-B7AE-D597024CDF06@microsoft.com>,
> "NJDevil" <NJDevil@discussions.microsoft.com> wrote:
>
> > Thanks for the reply. However, I don't know how to use that. It sounds
> > like I need to do some programming to get the info I need. Can you PLEASE
> > provide me with some code (or how ever you would use it) so that I may
> > integrate that solution into my spreadsheet? I have spend a lot of time on
> > this spreadheet and this is the one thing that I really need to make it do
> > what I need!
> >
> > Once I have the code, how do I invoke it in Excel? Sorry for being sucha
> > newby but I'm not an Excel programmer. I really wish I could just invoke a
> > function, but the '=CELL' function does not let me query the indent length.
>

Since you're working with the activecell, you'll be working with the
activesheet--so you don't need that variable for the worksheet. Code in a
general module that has ranges that are unqualified will refer to the
activesheet.

And since you're activating cells, you don't need to keep track of rows and
columns

This may do what you want:

Option Explicit
Sub query_indent()

Cells(2, 1).Select
Do While Trim(ActiveCell.Value) <> ""
ActiveCell.Offset(0, 2).Value = ActiveCell.IndentLevel
ActiveCell.Offset(1, 0).Select
Loop

End Sub

Another way to do it with selecting or activating:

Option Explicit
Sub query_indent2()

Dim wrkSheet As Worksheet
Dim myRng As Range
Dim myCell As Range

Set wrkSheet = Worksheets("sheet1")

With wrkSheet

Set myRng = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp))

For Each myCell In myRng.Cells
With myCell
.Offset(0, 2).Value = .IndentLevel
End With
Next myCell

End With

End Sub


But this actually checks the cells in A2 through the last used cell in column A.

This portion: .Cells(.Rows.Count, "A").End(xlUp)

is like selecting A65536 and then hitting the End key followed by the UpArrow.



NJDevil wrote:
>
> Thanks JE. I've been reading how to define and setup a amcro from some info
> on the web...
>
> http://msdn.microsoft.com/library/de.../odc_super.asp
>
> I think I'm on the right track.
>
> Can you have a look at this and make some adjustments to get it to work?
> Basically Im just trying to put the indent level in a separate column.
> THANKS FOR YOUR HELP!
>
> Sub query_indent()
>
>
> Dim indent_length
> Dim Row
> Dim Col
>
> 'Not sure if I need all three Dim statements above.
>
>
> 'Select the active worksheet
> Set wrkSheet = ActiveWorkbook.ActiveSheet
>
> 'indent_length = ActiveCell.IndentLevel
>
> 'Start on the second row since the top row is the header
> 'Range("C2").Value = indent_length
>
> 'Start at A2 (i.e. Row 2, Col A)
> Row = 2
> Col = 1
> Contents = ActiveCell.Address(Row, Col).Cell.Value
>
> 'It does not like the above statement... can I use Row and Col variables?
> 'If not, how can I increment through it?
>
> Do While Contents <> " "
>
> indent_length = ActiveCell.IndentLevel
>
> 'Now place the indent length in Col C of the same row
> ActiveCell.Address(Row, Col + 2).Cell.Select
> ActiveCell.Value = indent_length
>
> 'Increment to the next row
> Row = Row + 1
>
> 'Grab the data in the next cell (next row, same col)
> ActiveCell.Address(Row, Col).Cell.Select
> Loop
>
> End Sub
>
> I guess you can think of the above as psuedo code, but if you can help me
> get it right I would certainly appreciate it!
>
> Thank you again. It's 1;10am here (NJ) and I'm going to call it a night.
>
> Cheers!
>
> "JE McGimpsey" wrote:
>
> > Make Jim's solution into a UDF. As you're not familiar with UDFs, read
> > David McRitchie's "Getting Started with Macros and User Defined
> > Functions":
> >
> >
> > http://www.mvps.org/dmcritchie/excel/getstarted.htm
> >
> >
> > Public Function IndentLevel(Ref As Range) As Long
> > Application.Volatile
> > IndentLevel = Ref.IndentLevel
> > End Function
> >
> >
> > Call from the worksheet as
> >
> > B1: =IndentLevel(A1)
> >
> > to find the indent level of cell A1. Note that even with the
> > Application.Volatile command, which makes the function recalculate
> > whenever the worksheet is recalculated, changing the indent level
> > doesn't fire a worksheet calculation, so you'll need to use F9 to be
> > sure it's accurate after changing an indent level.
> >
> > In article <952FB8FA-4144-42FD-B7AE-D597024CDF06@microsoft.com>,
> > "NJDevil" <NJDevil@discussions.microsoft.com> wrote:
> >
> > > Thanks for the reply. However, I don't know how to use that. It sounds
> > > like I need to do some programming to get the info I need. Can you PLEASE
> > > provide me with some code (or how ever you would use it) so that I may
> > > integrate that solution into my spreadsheet? I have spend a lot of time on
> > > this spreadheet and this is the one thing that I really need to make it do
> > > what I need!
> > >
> > > Once I have the code, how do I invoke it in Excel? Sorry for being sucha
> > > newby but I'm not an Excel programmer. I really wish I could just invoke a
> > > function, but the '=CELL' function does not let me query the indent length.
> >

--

Dave Peterson

Hi JE, I haev not gotten it to work. It return a zero no matter if it's
indented or not. I will try to figure it out later. For now one of the
other posts (macro in VBA) works.

Thaks very much for helping me in any case. I very much appreciate it!

NJDevil

"JE McGimpsey" wrote:

> Make Jim's solution into a UDF. As you're not familiar with UDFs, read
> David McRitchie's "Getting Started with Macros and User Defined
> Functions":
>
>
> http://www.mvps.org/dmcritchie/excel/getstarted.htm
>
>
> Public Function IndentLevel(Ref As Range) As Long
> Application.Volatile
> IndentLevel = Ref.IndentLevel
> End Function
>
>
> Call from the worksheet as
>
> B1: =IndentLevel(A1)
>
> to find the indent level of cell A1. Note that even with the
> Application.Volatile command, which makes the function recalculate
> whenever the worksheet is recalculated, changing the indent level
> doesn't fire a worksheet calculation, so you'll need to use F9 to be
> sure it's accurate after changing an indent level.
>
> In article <952FB8FA-4144-42FD-B7AE-D597024CDF06@microsoft.com>,
> "NJDevil" <NJDevil@discussions.microsoft.com> wrote:
>
> > Thanks for the reply. However, I don't know how to use that. It sounds
> > like I need to do some programming to get the info I need. Can you PLEASE
> > provide me with some code (or how ever you would use it) so that I may
> > integrate that solution into my spreadsheet? I have spend a lot of time on
> > this spreadheet and this is the one thing that I really need to make it do
> > what I need!
> >
> > Once I have the code, how do I invoke it in Excel? Sorry for being sucha
> > newby but I'm not an Excel programmer. I really wish I could just invoke a
> > function, but the '=CELL' function does not let me query the indent length.
>

JE,

After getting the macro to work and refined, I tried your way (after
learning more about UDFs). It works great. Yes, F9 must be used, but it's
very stratight forward and the cell is sort of self documenting.

Thank you!

NJDevil

"JE McGimpsey" wrote:

> Make Jim's solution into a UDF. As you're not familiar with UDFs, read
> David McRitchie's "Getting Started with Macros and User Defined
> Functions":
>
>
> http://www.mvps.org/dmcritchie/excel/getstarted.htm
>
>
> Public Function IndentLevel(Ref As Range) As Long
> Application.Volatile
> IndentLevel = Ref.IndentLevel
> End Function
>
>
> Call from the worksheet as
>
> B1: =IndentLevel(A1)
>
> to find the indent level of cell A1. Note that even with the
> Application.Volatile command, which makes the function recalculate
> whenever the worksheet is recalculated, changing the indent level
> doesn't fire a worksheet calculation, so you'll need to use F9 to be
> sure it's accurate after changing an indent level.
>
> In article <952FB8FA-4144-42FD-B7AE-D597024CDF06@microsoft.com>,
> "NJDevil" <NJDevil@discussions.microsoft.com> wrote:
>
> > Thanks for the reply. However, I don't know how to use that. It sounds
> > like I need to do some programming to get the info I need. Can you PLEASE
> > provide me with some code (or how ever you would use it) so that I may
> > integrate that solution into my spreadsheet? I have spend a lot of time on
> > this spreadheet and this is the one thing that I really need to make it do
> > what I need!
> >
> > Once I have the code, how do I invoke it in Excel? Sorry for being sucha
> > newby but I'm not an Excel programmer. I really wish I could just invoke a
> > function, but the '=CELL' function does not let me query the indent length.
>

JE,

Can you tell me where the UDF is stored? I can't find it in the macro list
where it was created, I don't have the function button available nor can I
seem to find that either (i.e. f* button).

Thanks.

NJDevil

"NJDevil" wrote:

> JE,
>
> After getting the macro to work and refined, I tried your way (after
> learning more about UDFs). It works great. Yes, F9 must be used, but it's
> very stratight forward and the cell is sort of self documenting.
>
> Thank you!
>
> NJDevil
>
> "JE McGimpsey" wrote:
>
> > Make Jim's solution into a UDF. As you're not familiar with UDFs, read
> > David McRitchie's "Getting Started with Macros and User Defined
> > Functions":
> >
> >
> > http://www.mvps.org/dmcritchie/excel/getstarted.htm
> >
> >
> > Public Function IndentLevel(Ref As Range) As Long
> > Application.Volatile
> > IndentLevel = Ref.IndentLevel
> > End Function
> >
> >
> > Call from the worksheet as
> >
> > B1: =IndentLevel(A1)
> >
> > to find the indent level of cell A1. Note that even with the
> > Application.Volatile command, which makes the function recalculate
> > whenever the worksheet is recalculated, changing the indent level
> > doesn't fire a worksheet calculation, so you'll need to use F9 to be
> > sure it's accurate after changing an indent level.
> >
> > In article <952FB8FA-4144-42FD-B7AE-D597024CDF06@microsoft.com>,
> > "NJDevil" <NJDevil@discussions.microsoft.com> wrote:
> >
> > > Thanks for the reply. However, I don't know how to use that. It sounds
> > > like I need to do some programming to get the info I need. Can you PLEASE
> > > provide me with some code (or how ever you would use it) so that I may
> > > integrate that solution into my spreadsheet? I have spend a lot of time on
> > > this spreadheet and this is the one thing that I really need to make it do
> > > what I need!
> > >
> > > Once I have the code, how do I invoke it in Excel? Sorry for being sucha
> > > newby but I'm not an Excel programmer. I really wish I could just invoke a
> > > function, but the '=CELL' function does not let me query the indent length.
> >

How about
Len(yourcell) - Trim(yourcell)

The result would be the number of leading spaces provided you have no
other irregular spacing in the cell.

"Ctal" wrote:

> How about
> Len(yourcell) - Trim(yourcell)
>
> The result would be the number of leading spaces provided you have no
> other irregular spacing in the cell.
>
> Thanks, but there are no leading blanks. The indent does create a leading character. For example, I tried:

=len(a1) - len(trim(a1)

the function worked, but it results in a zero (0) being returned.

If I put a space in front then it will return a one (1).

Thank you for taking the time to respond though!