Hi Everybody,
I have a array as Sheet1.Range("A :G50000") which I keep all my data.
Now I try to extract some data into listbox as between two dates by
using combo boxes.My comboboxes have date values.Every time I trigger
command button seems like the data are not correct in the listbox.
If you can give me a hand here, I will be highly appreciated. Here is
my code
Thanks in advance
Baha
Private Sub ComboBox4_Change()
ComboBox4.Value = Format(ComboBox4.Value, "MMM/YYYY")
ListBox2.Clear
End Sub
Private Sub ComboBox5_Change()
ComboBox5.Value = Format(ComboBox5.Value, "MMM/YYYY")
ListBox2.Clear
End Sub
Private Sub CommandButton9_Click()
Dim cell As Range
ListBox2.Clear
ListBox2.ColumnCount = 6
On Error Resume Next
For Each cell In Sheet2.Range("A2:C500").Columns(2).Cells
If Format(cell.Value, "MMM/YYYY") >
Format(ComboBox4.Value, "MMM/YYYY") _
And Format(cell.Value, "MM/DD/YYYY") <
Format(ComboBox5.Value, "MM/DD/YYYY") Then
ListBox2.ColumnCount = 6
ListBox2.AddItem cell.Offset(0, -1)
ListBox2.List(ListBox2.ListCount - 1, 1) = _
Application.WorksheetFunction.VLookup(cell.Offset(0, -1),
Sheet1.Range("A1:G50000"), 2, 0)
ListBox2.List(ListBox2.ListCount - 1, 2) = cell.Offset(0,
2)
ListBox2.List(ListBox2.ListCount - 1, 3) = _
Application.WorksheetFunction.SumIf(Sheet2.Range("A2:A50000"),
cell.Offset(0, -1), Sheet2.Range("C2:C50000"))
ListBox2.List(ListBox2.ListCount - 1, 4) = cell.Text
ListBox2.TextColumn = 1
End If
Next cell
End Sub
Private Sub UserForm_Initialize()
Dim maxdeg As Variant
Dim dteDate As Date
For dteDate = #1/1/2009# To #2/28/2012#
ComboBox4.AddItem Format(dteDate, "MM/DD/YYYY")
ComboBox5.AddItem Format(dteDate, "MM/DD/YYYY")
Next dteDate
Next
End Sub
|
