Indent based on criteria question

spacecaptainsuperguy

Board Regular
Joined
Dec 30, 2004
Messages
173
I am looking to format a report with successive indents and hope someone can put me on the right path. A previous report I've been working on uses a loop to find headers and make them bold. I figure something like that may work for this, but am unsure.

Here's what I'm wanting to do:
1st look for the word ASSET and begin highlighting every row after it and stop one row prior to finding the word TOTAL ASSETS and indent.

Next look for the word CURRENT ASSETS and begin highlighting every row after it and stop one row prior to finding the word TOTAL CURRENT ASSETS and indent.

I'll go through this a couple more times looking for all the various headers and sub headers under the asset category and then do the whole process over again for the liabilites and equity section.


My prior solution was to just record a macro and do the steps which worked fine until a new line item showed up or an old one dissapeared and screwed up the macro. Now I'm spending more time correcting the macro every month than if I just did it by hand. Hopefully this will be a permanent solution.

thanks in advance for you help
SpaceCap
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
Even though I am not into VBA, I believe that you will receive the help you need if you post your present code, so the knowledgeable folk here can do simple insertions of changes to your code. It is very difficult to, out of the blue, post a solution that works, if the question has only vague descriptions of the spreadsheet.
 

spacecaptainsuperguy

Board Regular
Joined
Dec 30, 2004
Messages
173
I didn't realize anyone would want to see it. This is the macro I recorded and have had to continually tweak based on the changes to the number of rows in the report over time. Hope you can find this useful.
SpaceCap


Sub HOA_BS()
'
' HOA_BS Macro
' Macro recorded 10/14/2005 by SpaceCaptainSuperGuy
'

'
ActiveCell.FormulaR1C1 = "CT Ranch Community Association"
Range("A1:C2").Select
Selection.UnMerge
Columns("A:A").ColumnWidth = 53
Columns("B:C").Select
Selection.NumberFormat = "#,##0.00_);(#,##0.00)"
Range("A6:A18").Select
Selection.InsertIndent 1
Range("A7:A16").Select
Selection.InsertIndent 1
Range("A8:A12").Select
Selection.InsertIndent 1
Range("A15").Select
Selection.InsertIndent 1
Range("A20:A26").Select
Selection.InsertIndent 1
Range("A21:A22").Select
' Range("A23").Activate
Selection.InsertIndent 1
Range("A25:A25").Select
Selection.InsertIndent 1
Range("6:6,7:7,14:14").Select
Range("A14").Activate
Selection.Insert Shift:=xlDown
Rows("20:20").Select
Selection.Insert Shift:=xlDown
Rows("22:22").Select
Selection.Insert Shift:=xlDown
Rows("24:25").Select
Selection.Insert Shift:=xlDown
Rows("27:27").Select
Selection.Insert Shift:=xlDown
ActiveWindow.SmallScroll Down:=6
Rows("32:32").Select
Selection.Insert Shift:=xlDown
Rows("36:36").Select
Selection.Insert Shift:=xlDown
ActiveWindow.SmallScroll Down:=-13
' Range("6:6,8:8,16:16,20:20,22:22,27:27,32:32").Select
' Range("A32").Activate
ActiveWindow.SmallScroll Down:=18
Range("6:6,8:8,16:16,20:20,22:22,27:27,32:32,36:36").Select
Range("A36").Activate
Selection.RowHeight = 8
ActiveWindow.SmallScroll Down:=-98
Range("A5,A23,A26,A37").Select
Range("A37").Activate
Selection.Font.bold = True
Range("B14:C14").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("B18:C18").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("B20:C20").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("B30:C30").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("B34:C34").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("B23:C23,B37:C37").Select
Range("B37").Activate
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Selection.ColumnWidth = 2
Range("A1:D1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("A2:D2").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
ActiveWindow.DisplayGridlines = True
Range("A1:D2").Select
Selection.UnMerge
Columns("E:E").Select
Selection.Insert Shift:=xlToRight
Columns("C:C").Select
Selection.Copy
Columns("E:E").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Columns("D:D").Select
Selection.Copy
Columns("F:F").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.ColumnWidth = 13.57
Rows("4:4").EntireRow.AutoFit
Columns("F:F").EntireColumn.AutoFit
Range("A1:F1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("A2:F2").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Rows("3:3").Select
Selection.Insert Shift:=xlDown
Range("A1:F1").Select

With ActiveSheet.PageSetup
' .LeftHeader = ""
' .CenterHeader = ""
' .RightHeader = ""
.LeftFooter = "Printed on &D"
' .CenterFooter = ""
.RightFooter = "Page &P"
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.75)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
' .PrintHeadings = False
' .PrintGridlines = False
' .PrintComments = xlPrintNoComments
' .PrintQuality = -1
.CenterHorizontally = True
' .CenterVertically = False
.Orientation = xlLandscape
' .Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
' .BlackAndWhite = False
' .Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
.PrintErrors = xlPrintErrorsDisplayed
End With

ActiveSheet.Name = "Balance Sheet"
Sheets("Balance Sheet").Select

Range("B4:F5").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
End Sub
 

spacecaptainsuperguy

Board Regular
Joined
Dec 30, 2004
Messages
173
By the way...sorry...I don't know how to post such that the formatting of the macro is easier to look at.
SpaceCap
 

xapie128

Board Regular
Joined
Aug 26, 2006
Messages
82
By the way...sorry...I don't know how to post such that the formatting of the macro is easier to look at.
SpaceCap
When you post, precede the macro with the word code in brackets [ ] then end it with /code in brackets (I can't type it out here because it would actually do it instead of explain what it looks like). You can also use the Code button above the box where you compose your post, but you have to watch out because it won't place the BBCode (that is the words inside the brackets) at the cursor, but always at the end of the post, but you can hand-type it anywhere the cursor is.

Without using Code it will left justify every line. Examples:
A
B
C
D
E
F
G
Code:
A
  B
    C
      D
    E
  F
G
Both above were typed with leading spaces, but only the second one displays the leading spaces.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,952
Messages
5,545,143
Members
410,666
Latest member
Al3cs
Top