Perfect! Thanks so much for the help and the quick reply.

This is fantastic!

NJDevil

"Dave Peterson" wrote:

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!)

NJDevil wrote:

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

"Dave Peterson" wrote:

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.

NJDevil wrote:

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

"Dave Peterson" wrote:

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 ,
"NJDevil" 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


--

Dave Peterson


--

Dave Peterson