AccountCode 01-80-07 03-24-00 True 03-24-00 03-69-00 04-16-00 True 04-20-00 04-21-00 05-99-00 True 07-01-00 True 07-01-00 07-10-00 07-10-00 94-40-95 True
Does anyone understand what I need? Trues are in column B and each month there may be a different number of trues and different spacing between them.
Write a function that generates an array of all row numbers where "True" appears on column B, then go through the array backwards (treat last element first), adding lines wherever needed. This way you don't need to keep track of the changes you make after creating the array.
Edit: Code Skeleton
Sub DoTheThing()
Dim indices() As Integer
indices = getIndices
Dim i As Integer
For i = Len(indices) - 1 To 0 Step -1
Call AddRows(indices(i))
Next i
End Sub
Sub AddRows(index As Integer)
' Add two rows above the passed row index. '
End Sub
Function getIndices() As Integer()
' Gather all indices of "True" rows to an array. '
End Function
I would just create a "Row" column that uses a formula to see if there is a TRUE next to an Account Code, and if there is, keeps the row number the same for two additional rows. If there isn't, then it just advances to the next row.
Row AccountCode 1 01-80-07 2 2 2 03-24-00 TRUE 3 03-24-00 4 03-69-00 5 5 5 04-16-00 TRUE 6 04-20-00 7 04-21-00 8 8 8 05-99-00 TRUE 9 9 9 07-01-00 TRUE 10 07-01-00 11 07-10-00 12 07-10-00 13 13 13 94-40-95 TRUE
Here are the formulas you need. The "Row" label should be in cell D2.
Row AccountCode 1 =IF(D3=D4,"",INDEX(A$2:A$14,D3)) =IF(D3=D4,"",IF(INDEX(B$2:B$14,D3)=TRUE,TRUE,"")) =IF(INDEX(B$2:B$14,D3),IF(D1=D3,D3+1,D3),D3+1) =IF(D4=D5,"",INDEX(A$2:A$14,D4)) =IF(D4=D5,"",IF(INDEX(B$2:B$14,D4)=TRUE,TRUE,"")) =IF(INDEX(B$2:B$14,D4),IF(D2=D4,D4+1,D4),D4+1) =IF(D5=D6,"",INDEX(A$2:A$14,D5)) =IF(D5=D6,"",IF(INDEX(B$2:B$14,D5)=TRUE,TRUE,"")) =IF(INDEX(B$2:B$14,D5),IF(D3=D5,D5+1,D5),D5+1) =IF(D6=D7,"",INDEX(A$2:A$14,D6)) =IF(D6=D7,"",IF(INDEX(B$2:B$14,D6)=TRUE,TRUE,"")) =IF(INDEX(B$2:B$14,D6),IF(D4=D6,D6+1,D6),D6+1) =IF(D7=D8,"",INDEX(A$2:A$14,D7)) =IF(D7=D8,"",IF(INDEX(B$2:B$14,D7)=TRUE,TRUE,"")) =IF(INDEX(B$2:B$14,D7),IF(D5=D7,D7+1,D7),D7+1) =IF(D8=D9,"",INDEX(A$2:A$14,D8)) =IF(D8=D9,"",IF(INDEX(B$2:B$14,D8)=TRUE,TRUE,"")) =IF(INDEX(B$2:B$14,D8),IF(D6=D8,D8+1,D8),D8+1) =IF(D9=D10,"",INDEX(A$2:A$14,D9)) =IF(D9=D10,"",IF(INDEX(B$2:B$14,D9)=TRUE,TRUE,"")) =IF(INDEX(B$2:B$14,D9),IF(D7=D9,D9+1,D9),D9+1) =IF(D10=D11,"",INDEX(A$2:A$14,D10)) =IF(D10=D11,"",IF(INDEX(B$2:B$14,D10)=TRUE,TRUE,"")) =IF(INDEX(B$2:B$14,D10),IF(D8=D10,D10+1,D10),D10+1) =IF(D11=D12,"",INDEX(A$2:A$14,D11)) =IF(D11=D12,"",IF(INDEX(B$2:B$14,D11)=TRUE,TRUE,"")) =IF(INDEX(B$2:B$14,D11),IF(D9=D11,D11+1,D11),D11+1) =IF(D12=D13,"",INDEX(A$2:A$14,D12)) =IF(D12=D13,"",IF(INDEX(B$2:B$14,D12)=TRUE,TRUE,"")) =IF(INDEX(B$2:B$14,D12),IF(D10=D12,D12+1,D12),D12+1) =IF(D13=D14,"",INDEX(A$2:A$14,D13)) =IF(D13=D14,"",IF(INDEX(B$2:B$14,D13)=TRUE,TRUE,"")) =IF(INDEX(B$2:B$14,D13),IF(D11=D13,D13+1,D13),D13+1) =IF(D14=D15,"",INDEX(A$2:A$14,D14)) =IF(D14=D15,"",IF(INDEX(B$2:B$14,D14)=TRUE,TRUE,"")) =IF(INDEX(B$2:B$14,D14),IF(D12=D14,D14+1,D14),D14+1) =IF(D15=D16,"",INDEX(A$2:A$14,D15)) =IF(D15=D16,"",IF(INDEX(B$2:B$14,D15)=TRUE,TRUE,"")) =IF(INDEX(B$2:B$14,D15),IF(D13=D15,D15+1,D15),D15+1) =IF(D16=D17,"",INDEX(A$2:A$14,D16)) =IF(D16=D17,"",IF(INDEX(B$2:B$14,D16)=TRUE,TRUE,"")) =IF(INDEX(B$2:B$14,D16),IF(D14=D16,D16+1,D16),D16+1) =IF(D17=D18,"",INDEX(A$2:A$14,D17)) =IF(D17=D18,"",IF(INDEX(B$2:B$14,D17)=TRUE,TRUE,"")) =IF(INDEX(B$2:B$14,D17),IF(D15=D17,D17+1,D17),D17+1) =IF(D18=D19,"",INDEX(A$2:A$14,D18)) =IF(D18=D19,"",IF(INDEX(B$2:B$14,D18)=TRUE,TRUE,"")) =IF(INDEX(B$2:B$14,D18),IF(D16=D18,D18+1,D18),D18+1) =IF(D19=D20,"",INDEX(A$2:A$14,D19)) =IF(D19=D20,"",IF(INDEX(B$2:B$14,D19)=TRUE,TRUE,"")) =IF(INDEX(B$2:B$14,D19),IF(D17=D19,D19+1,D19),D19+1) =IF(D20=D21,"",INDEX(A$2:A$14,D20)) =IF(D20=D21,"",IF(INDEX(B$2:B$14,D20)=TRUE,TRUE,"")) =IF(INDEX(B$2:B$14,D20),IF(D18=D20,D20+1,D20),D20+1) =IF(D21=D22,"",INDEX(A$2:A$14,D21)) =IF(D21=D22,"",IF(INDEX(B$2:B$14,D21)=TRUE,TRUE,"")) =IF(INDEX(B$2:B$14,D21),IF(D19=D21,D21+1,D21),D21+1) =IF(D22=D23,"",INDEX(A$2:A$14,D22)) =IF(D22=D23,"",IF(INDEX(B$2:B$14,D22)=TRUE,TRUE,"")) =IF(INDEX(B$2:B$14,D22),IF(D20=D22,D22+1,D22),D22+1) =IF(D23=D24,"",INDEX(A$2:A$14,D23)) =IF(D23=D24,"",IF(INDEX(B$2:B$14,D23)=TRUE,TRUE,"")) =IF(INDEX(B$2:B$14,D23),IF(D21=D23,D23+1,D23),D23+1) =IF(D24=D25,"",INDEX(A$2:A$14,D24)) =IF(D24=D25,"",IF(INDEX(B$2:B$14,D24)=TRUE,TRUE,"")) =IF(INDEX(B$2:B$14,D24),IF(D22=D24,D24+1,D24),D24+1) =IF(D25=D26,"",INDEX(A$2:A$14,D25)) =IF(D25=D26,"",IF(INDEX(B$2:B$14,D25)=TRUE,TRUE,""))