Counting Indents

[MParham]

I have a Hyperion report that gives me the Chart of Accounts in a way that
each level is indented when viewed in Excel. There are no spaces to count, it
is all done with indents. I need to convert the file dividing them out into
Level groups so I need to know how many times the item was indented so I can
write a macro or formula to move it to the proper level. When an item is
indented, how do you get the computer to calculate the number of indents? If
you can't do that, can I replace each indent with another character I can
count via formula?

An example of what I have is:

Account 1
Account 1a
Account 1b
Account 1b1
Account 2

That is just something to show how my report comes in. I used spaces in the
above example but the report does not, it uses indents and I need to be able
to calculate how many indents on each Account so I can use another formula to
automatically place it in the right place of another form.

 

[Gary''s Student]

This is a great question!

Say we have a cell with indented contents and want to know the indent level.
Consider the following one-line UDF:

Function indenture(r As Range) As Integer
indenture = r.IndentLevel
End Function

If A1 has been indented 3 times,
=indenture(A1) will return a 3

 

[Jim Cone]

I haven't worked with Hyperion, but am surprised that a non-excel program
provides text indents that Excel recognizes as such.
I am not aware of any Excel function or formula that returns an indent level.
The vba phrase you need is "IndentLevel";
it is a property of the range object. (and Style object).
It can be used as... Cells(2, 4).IndentLevel

A vba worksheet function you can call from the cell immediately
to the right of the indented cell is...
'--
Function HowMany() As Double
On Error GoTo NotEnough
Application.Volatile
HowMany = Application.Caller.Offset(0, -1).IndentLevel
Exit Function
NotEnough:
HowMany = 9999
End Function
--
Note - changing the indent level requires a sheet calculation to update the function.
Jim Cone
Portland, Oregon USA



"MParham"
<[email protected]>
wrote in message
I have a Hyperion report that gives me the Chart of Accounts in a way that
each level is indented when viewed in Excel. There are no spaces to count, it
is all done with indents. I need to convert the file dividing them out into
Level groups so I need to know how many times the item was indented so I can
write a macro or formula to move it to the proper level. When an item is
indented, how do you get the computer to calculate the number of indents? If
you can't do that, can I replace each indent with another character I can
count via formula?

An example of what I have is:

Account 1
Account 1a
Account 1b
Account 1b1
Account 2

That is just something to show how my report comes in. I used spaces in the
above example but the report does not, it uses indents and I need to be able
to calculate how many indents on each Account so I can use another formula to
automatically place it in the right place of another form.

 

[MParham]

I appreciate your help on that one. It worked out beautifully. I am still
learning the Macros and Visual Basic and I am getting better. I do have
another question related to this one. When i created my Macros I did not have
to do anything special for them to affect any active journal. The Function
seems only to affect the journal I created it in, even though it is the one
that automatically loads everytime I boot Excel with all my Macros. I can't
seem to make the Function globally available. How do i make it so i can use
this functionon in any journal I am working on?