sujittalukde
11-19-2013, 04:29 AM
I am suing the following code to populate data from sheet named "Transaction" based on condition. It is adding only the data occurring in the Transaction sheet though there are more than one record in the sheet that matching the criteria.
How can I add all the matched records in the list box?
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
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 = TextBox2.Text
tFY1 = TextBox3.Text
tProject1 = ComboBox1.Text
tSection1 = ComboBox2.Text
tMonth1 = ComboBox3.Text
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
thanks.
How can I add all the matched records in the list box?
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
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 = TextBox2.Text
tFY1 = TextBox3.Text
tProject1 = ComboBox1.Text
tSection1 = ComboBox2.Text
tMonth1 = ComboBox3.Text
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
thanks.