How do I find all "true" in a column and insert two rows above each found true?_问答_开发者

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 开发者_StackOverflow中文版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,""))

VBA Pseudocode (may not compile):

Public Sub InsertRowBeforeEachTrue(ws As Worksheet, checkColumn As Integer)

    Dim lastRow As Integer
    lastRow = ws.UsedRange.Rows.Count 

    Dim i As Integer
    Dim cell As Range

    For i = lastRow to 1 Step -1
        Set cell = ws.Cells(i, checkColumn)
        If cell.Value = true Then 
            cell.InsertRow
            cell.InsertRow
        End If
    Next i

End Sub