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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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 said:
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:
Click to expand...

=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!
 
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
 
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.
 
Thanks JE. I've been reading how to define and setup a amcro from some info
on the web...

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odc_xl2003_ta/html/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!
 
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.
 
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
 
Dave,

Halleluiah! I like the first one and I will use it. Thank you so much!

One more thing, can you tell me how I can invoke the macro without all the
mouse clicks to take me to the 'run' button in the macro setup? Is there a
way to associate a key combination (e.g. ctl+I) to execute the desired macro?

Thanks fror the big help!

NJDevil
 
I liked the second one <bg>.

Start up excel. Load the file with the macro.
Tools|macro|macros...
Select your macro from the list.
Click options
Assign the shortcut key you want to use.
Click ok to exit that dialog.

Click Cancel to get out of the next dialog.

Don't forget to save your workbook with your change.
 
Hello again Dave,

I got it to work, but now I'd like to enhance it. What I'd like to do is to
put my cursor back in the cell I was in before I invoked the macro. This
should be very easy to do, but I don't know how. Psuedo code would go
something like this:

mycell = location of the active cell.

{peform the rest of the macro here}

activecell = mycell

I would greatly appreciate if you would please provide some code to do it.
Also, is there a way to popup a message that says 'Indent Recalculated"?

THANK YOU!

NJ Devil
 
That's what the second suggestion did...

But you could use:

Option Explicit
Sub query_indent()

Dim CurCell As Range
Dim CurSelection As Range

Set CurCell = ActiveCell
Set CurSelection = Selection

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

CurSelection.Select
CurCell.Activate
End Sub

(I still like that second suggestion!)
Hello again Dave,

I got it to work, but now I'd like to enhance it. What I'd like to do is to
put my cursor back in the cell I was in before I invoked the macro. This
should be very easy to do, but I don't know how. Psuedo code would go
something like this:

mycell = location of the active cell.

{peform the rest of the macro here}

activecell = mycell

I would greatly appreciate if you would please provide some code to do it.
Also, is there a way to popup a message that says 'Indent Recalculated"?

THANK YOU!

NJ Devil
Click to expand...
 
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,

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
 
Back
Top