key in date and filter the data in a week
Hi all,
I have question on VBA which is quite difficult for me. I need a VBA code that can filter out all data in a specific week after i type in a date, any date within that week in a textbox which i have previously created. For example i have a table of dates corresponding to number of week:
| Week 1 | 02-Jan-2006 | 09-Jan-2006 |
| Week 2 | 09-Jan-2006 | 16-Jan-2006 |
| Week 3 | 16-Jan-2006 | 23-Jan-2006 |
| Week 4 | 23-Jan-2006 | 30-Jan-2006 |
| Week 5 | 30-Jan-2006 | 06-Feb-2006 |
Week 6
| 06-Feb-2006 | 13-Feb-2006 |
Week 7
| 13-Feb-2006 | 20-Feb-2006 |
| Week 8 | 20-Feb-2006 | 27-Feb-2006 |
| Week 9 | 27-Feb-2006 | 06-Mar-2006 |
and a source data which i want to filter:
| Date | Input | Output |
| 05-Jan-2006 | 776.95 | 720.82 |
| 07-Jan-2006 | 747.84 | 729.43 |
| 08-Jan-2006 | 755.49 | 745.80 |
| 25-Jan-2006 | 800.12 | 775.75 |
| 30-Jan-2006 | 794.96 | 775.03 |
| 12-Feb-2006 | 787.62 | 758.65 |
| 14-Feb-2006 | 800.77 | 783.94 |
| 19-Feb-2006 | 786.89 | 750.06 |
| 22-Feb-2006 | 769.86 | 762.24 |
Now if i type in a date, for example 06-Jan-2006 in the textbox ( in a userform), i want the macro to help me to filter out the data of the week of 06-Jan-2006, which is week1, in the following form:
| Date | Input | Output |
| 05-Jan-2006 | 776.95 | 720.82 |
| *07-Jan-2006 | 747.84 | 729.43 |
| 08-Jan-2006 | 755.49 | 745.80 |
| 25-Jan-2006 | 800.12 | 775.75 |
Is this possible?
I would like to thank in advance to anyone who willing to help me on this. THANKS a lot!! :)
[7151 byte] By [
pippen] at [2007-12-23]
Hi Pippen
I copied your weeks table into Sheet1 starting at A1, I created a dropdown selection box in K1 ( with various dates), and copied your sample data into sheet2 starting in F1. The below code picks up any change to Sheet1 K1 (the dropdown box) and tries to find the range of dates, if the range is found it applies the Excel filter to the data on sheet 2
Hope this helps
Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As Long
Dim DateFound As Boolean
Dim SelectedDate, StartDate, EndDate As Date
If Intersect(Target, Sheet1.Range("K1")) Then
X = 1
DateFound = False
SelectedDate = Sheet1.Cells(1, 11).Value
While Left$(Sheet1.Cells(X, 1).Value, 4) = "Week" And Not DateFound
If ((SelectedDate >= Sheet1.Cells(X, 2).Value) And (SelectedDate <= Sheet1.Cells(X, 3).Value)) Then
StartDate = Sheet1.Cells(X, 2).Value
EndDate = Sheet1.Cells(X, 3).Value
DateFound = True
Else
X = X + 1
End If
Wend
If DateFound Then
Sheet2.Activate
Sheet2.Range("F1:H1").Select
Sheet2.Range(Selection, Selection.End(xlDown)).Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=">=" & Format(StartDate, "dd-mmm-yyyy"), Operator:=xlAnd _
, Criteria2:="<=" & Format(EndDate, "dd-mmm-yyyy")
End If
End If
End Sub
ADG at 2007-8-30 >
I managed to create a drop down list. However, the sheet2 doesn't filter itself. why?
Thanks and really appreciate your time for replying me. Thanks again.
 ADG wrote: |
Hi Pippen I copied your weeks table into Sheet1 starting at A1, I created a dropdown selection box in K1 ( with various dates), and copied your sample data into sheet2 starting in F1. The below code picks up any change to Sheet1 K1 (the dropdown box) and tries to find the range of dates, if the range is found it applies the Excel filter to the data on sheet 2 Hope this helps Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As Long
Dim DateFound As Boolean
Dim SelectedDate, StartDate, EndDate As Date
If Intersect(Target, Sheet1.Range("K1")) Then
X = 1
DateFound = False
SelectedDate = Sheet1.Cells(1, 11).Value
While Left$(Sheet1.Cells(X, 1).Value, 4) = "Week" And Not DateFound
If ((SelectedDate >= Sheet1.Cells(X, 2).Value) And (SelectedDate <= Sheet1.Cells(X, 3).Value)) Then
StartDate = Sheet1.Cells(X, 2).Value
EndDate = Sheet1.Cells(X, 3).Value
DateFound = True
Else
X = X + 1
End If
Wend
If DateFound Then
Sheet2.Activate
Sheet2.Range("F1:H1").Select
Sheet2.Range(Selection, Selection.End(xlDown)).Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=">=" & Format(StartDate, "dd-mmm-yyyy"), Operator:=xlAnd _
, Criteria2:="<=" & Format(EndDate, "dd-mmm-yyyy")
End If
End If
End Sub |
|
Do the filters appear at the top of the columns on sheet2? If so go into the custom option on the date column and see what is in there. If the filters do not appear then I suspect that the data in you dropdown is not being found in your table, in which case check all your dates are actually date formats
ADG at 2007-8-30 >
| ADG wrote: |
| Do the filters appear at the top of the columns on sheet2? If so go into the custom option on the date column and see what is in there. If the filters do not appear then I suspect that the data in you dropdown is not being found in your table, in which case check all your dates are actually date formats |
|
Hi,
Thanks! Sorry.. the reason that it couldn't filter out itself was i didn't link the sheets properly and i didn't copy the code to the correct work sheet. sorry.. now i have solved this problem with your help. I have changed and improve the code a bit, so that i can fit my work better. Anyway, thanks a lot! really thank you. thanks for your time. :)
