How to indent all strings that are not in all caps in the worksheet?

[max3732]

I have alternating columns of strings and numbers, with an empty column after every 2 columns, like this:

PROPERTY A PROPERTY B
REVENUE REVENUE
Item1 25,000 Item 1 5,000
Item 2 10,000 TOTAL REVENUE 5,000
TOTAL REVENUE 35,000

What I would like to do is indent all the strings that are not in call caps. So for the example above it would indent everything with the word "Item"

In Excel I know how to do a function that tests to see if it's all caps:

=IF(EXACT(K14,UPPER(K14)),"Do nothing","Indent")

However, I don't know how to do this in VBA and how to make it apply to all strings on the particular sheet. Any help would be greatly appreciated

[mikerickson]

You could use this macro, which will indent any cell that contains a lower case letter. Formulas that return a non-all upper case result will be replaced with a constant, the (indented) value.

Please Login or Register  to view this content.

[max3732]

Caps Indent Example.xlsm

I'm a little confused by the code. I tried with the real data and it ran, but didn't indent. I've uploaded a sample.

I'm not sure what you meant by "Formulas that return a non-all upper case result will be replaced with a constant, the (indented) value."

[mikerickson]

In the file you attached, all the cells with a lower case letter already have an indent (vbTab = CHAR(9)). If you want a different index character, you can change that in the indicated line in the code.
Also, a spurious line got inserted into the code. You should copy paste the code again.

If you have a formula in a cell like ="aB" & A1, and A1 holds "C" the routine will replace that formula with an indented "aBC". But as a constant, so that changing the value of A1 will not change the cell value.

[max3732]

I'm lost here. In the second example I made a mistake and had 1 line indented "office rent" in the 1st column. I don't see anything else on the sheet indented. May the problem is I don't understand how indenting works with VBA. What do you mean by (vbTab = CHAR(9))? I tried looking it up and "CHAR" has something to do with ASCII.

When I record indenting by changing the alignment it uses "Selection.InsertIndent 1"

There are no formulas in the strings I want to indent

[shg]

Another way:

Please Login or Register  to view this content.

[mikerickson]

Thanks shg, I didn't know about the IndentLevel property.

[shg]

Curiously, InsertIndent is one of those methods that works in a UDF; I use it to indent work breakdown structures.

Please Login or Register  to view this content.