Adding conditional Data to multi column listbox

[sujittalukde]

I am using the following code to populate data from sheet named "Transaction" based on condition. It is adding only the last row of data occurring in the Transaction sheet though there are more than one record in the sheet that matching the criteria.

Private Sub FillTranListBox1()
    Dim tTAN1 As String
    Dim tFY1 As String
    Dim tProject1 As String
    Dim tSection1 As String
    Dim tMonth1 As Long
    Dim tLrow1 As Long
    Dim tCell1 As Range
    Dim CumuTDS As Double
    Dim tListBoxCols1 As Long
    Dim ShtTran As Worksheet
Set ShtTran = ThisWorkbook.Sheets("Transactions")
     
    tListBoxCols1 = 46
    With ListBox1
        .Clear
        .ColumnCount = tListBoxCols1
        .ColumnWidths = "40;20;0;40;0;40;0;60;0;0;0;0;40;15;10;0;0;20;10;0;10;20;20;20;20;20;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;20;0;0;"
    End With
    tTAN1 = "CCCCCCCC"
    tFY1 = "2013-14"
    tProject1 = "INF"
    tSection1 = "194C"
    tMonth1 = 10
    tLrow1 = ShtTran.Range("A" & Rows.Count).End(xlUp).Row
    For Each tCell1 In ShtTran.Range("A2:A" & tLrow1)
        If tCell1.Offset(0, 7).Value = tTAN1 And _
        tCell1.Offset(0, 5).Value = tFY1 And _
        tCell1.Offset(0, 3).Value = tProject1 And _
        tCell1.Offset(0, 13).Value = tSection1 And _
        tCell1.Offset(0, 1).Value = tMonth1 And _
        tCell1.Offset(0, 42).Value = "Unpaid" Then
            With ListBox1
                .List = tCell1.EntireRow.Cells.Value
            End With
        End If
    Next tCell1
End Sub

Calling from command Button-

Private Sub CommandButton1_Click()
FillTranListBox1
End Sub

How can I add all the matched records in the list box?
A sample workbook in attached and also here
Adding conditional Data to multi column listbox

Please help.

 

[AlphaFrog]

Try this...

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] FillTranListBox1()
    [color=darkblue]Dim[/color] tTAN1 [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] tFY1 [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] tProject1 [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] tSection1 [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] tMonth1 [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] tLrow1 [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] i [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] CumuTDS [color=darkblue]As[/color] [color=darkblue]Double[/color]
    [color=darkblue]Dim[/color] tListBoxCols1 [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] ShtTran [color=darkblue]As[/color] Worksheet
[color=darkblue]Set[/color] ShtTran = ThisWorkbook.Sheets("Transactions")
     
    tListBoxCols1 = 46
    
    tTAN1 = "CCCCCCCC"
    tFY1 = "2013-14"
    tProject1 = "INF"
    tSection1 = "194C"
    tMonth1 = 10
    tLrow1 = ShtTran.Range("A" & Rows.Count).End(xlUp).Row
    
    [color=darkblue]With[/color] ListBox1
        .Clear
        .ColumnCount = tListBoxCols1
        .ColumnWidths = "40;20;0;40;0;40;0;60;0;0;0;0;40;15;10;0;0;20;10;0;10;20;20;20;20;20;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;20;0;0;"
        
        .List = ShtTran.Range("A2:A" & tLrow1).Resize(, tListBoxCols1).Value
    
        [color=darkblue]For[/color] i = .ListCount - 1 [color=darkblue]To[/color] 0 [color=darkblue]Step[/color] -1
            
            [color=darkblue]If[/color] [color=darkblue]Not[/color] (.List(i, 7) = tTAN1 And _
                    .List(i, 5) = tFY1 And _
                    .List(i, 3) = tProject1 And _
                    .List(i, 13) = tSection1 And _
                    .List(i, 1) = tMonth1 And _
                    .List(i, 42) = "Unpaid") [color=darkblue]Then[/color] .RemoveItem i
        [color=darkblue]Next[/color] i
    [color=darkblue]End[/color] [color=darkblue]With[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[sujittalukde]

thanks, its working.

 

[AlphaFrog]

You're welcome.

Notify in the cross-post you have a solution to avoid unnecessary duplicate effort.

 

[sujittalukde]

Notified.