Date Range (Starting & Ending Date) - syed shahzad
 

Hi,

I have a worksheet name is Data, having inventory information. and One
column is for the DatePurchased, where daily purchased material is
entered, since three months.

like this:

DatePurchased:
1 jan 07
2 jan 07
3 jan 07
14 jan 07
19 jan 07
25 jan 07
1 feb 07
5 feb 07
10 feb 07
25 feb 07
28 feb 07
3 march 07
5 march 07
till end of March 07 ............

Now I want to filter the data by using [StartDate] and [EndDate] so I
can get the specific records. What should I do. I used Advance Filter,
with this code, but I could not retrive the required data:

Sub Purchase_Query()

Sheets("Data").Select

Range("A5:P500").AdvancedFilter Action:=xlFilterCopy,
CriteriaRange:=Range( _
"AT2:BI3"), CopyToRange:=Range("AT5:BI500"), Unique:=False
' Range("A1").Select

End Sub


I used operators and < and <= and <= with the date , but I could
not get the specified records according to my requirement.

is there any way to feed StartingDate and EndingDate method, so I can
solve my problem.

I used this thing in Access Query, it was fine, but in Excel how to
do, please help me.

Thanks for your support in Advance.

Syed shahzad zafar
Madinah - KSA

Date Range (Starting & Ending Date) - syed shahzad
 

You can AutoFilter the dates in place using the Custom option. You can
then copy the filtered data elsewhere, as long as it isn't the rows
subject to the filter. VBA isn't needed.

Hth,
Merjet

Date Range (Starting & Ending Date) - syed shahzad
 

On Jun 26, 11:24 pm, merjet wrote:
You can AutoFilter the dates in place using the Custom option. You can
then copy the filtered data elsewhere, as long as it isn't the rows
subject to the filter. VBA isn't needed.

Hth,
Merjet

Date Material Name ItemCode Category
01-Feb Thermostat for A/C A10001 Air Conditioning
02-Feb Halogen Bulb H0001 Bulb
03-Feb Halogen Bulb small B20003 Bulb
04-Feb Halogen Bulb small C30001 Carpentry
05-Feb Heating Elements 5000 W H0002 Electrical
06-Feb Tube Light 40W EL40002 Electrical
07-Feb Stepler for Store EL 0098 Electrical
08-Feb Telephone Set from BTC EL 0099 Electrical
09-Feb Heating Elements 5000 W EL 0094 Electrical
10-Feb Heaters 300 W EL 0095 Electrical
11-Feb Elements 250 W EL 0096 Electrical
12-Feb New Steam Generator L20001 Laundry
13-Feb testing serial no. L20002 Laundry
14-Feb Laundry Tumbler L20003 Laundry
15-Feb sigma paint 44 test P 5001 Painting



I checked Auto Filter, but it shows only one record. Actually I need
all records between two dates sopose I need records from 3rd Feb to 13
Feb how I can get this result.

Starting date is: 3 Feb
Ending Date is 13 Feb

in between the above dates how many records I have I want to filter.

Pls help me in this regard.



syed shahzad zafar
Madinah - KSA

Date Range (Starting & Ending Date) - syed shahzad
 

Just follow my instructions. Highlight all the data. Use the menu Data
| AutoFilter.
Click on the down-arrow for the Date column and select Custom. A popup
will appear.
Choose "greater than or equal to" and enter 2-Feb. Choose "less than
or equal to" and enter 13-Feb.

Hth,
Merjet

Date Range (Starting & Ending Date) - syed shahzad
 

On Jun 27, 4:44 pm, merjet wrote:
Just follow my instructions. Highlight all the data. Use the menu Data
| AutoFilter.
Click on the down-arrow for the Date column and select Custom. A popup
will appear.
Choose "greater than or equal to" and enter 2-Feb. Choose "less than
or equal to" and enter 13-Feb.

Hth,
Merjet


Hi Dear Merjet,

Thanks a lot, I tried as per your instruction, it is working now. now
my problem is solved, now I can get the data what I want.

I am preparing a VBA Store Inventory Program having many userforms How
can I use the same procedure from the Userform by selecting [Begining
Date] and [Ending Date]. if you provide a VBA code for this procedure,
I will he highly appreciated.


Once again thank you to taking a time for me. Thanks a lot.

Regards.

Syed shahzad zafar
Madinah - KSA

Date Range (Starting & Ending Date) - syed shahzad
 

Assume a UserForm with two MonthView controls, one for the starting
date and one for the ending date, and a CommandButton to click after
using the MonthView controls. (If you use Calender Controls or
TextBoxes, the code would differ only slightly.) Put the following
code in the UserForm's code module.

Dim miEnd As Long

Private Sub CommandButton1_Click()
miEnd = Sheets("Sheet1").Range("A1").End(xlDown)
Sheets("Sheet1").Range("A1:A" & miEnd).AutoFilter _
Field:=1, Criteria1:="=" & MonthView1, _
Operator:=xlAnd, Criteria2:="<=" & MonthView2
'more code to copy filtered data elsewhere if desired
End Sub

Private Sub UserForm_Terminate()
'clear AutoFilter
Sheets("Sheet1").Range("A1:A" & miEnd).AutoFilter
End Sub

Hth,
Merjet

Date Range (Starting & Ending Date) - syed shahzad
 

On Jun 27, 11:00 pm, merjet wrote:
Assume a UserForm with two MonthView controls, one for the starting
date and one for the ending date, and a CommandButton to click after
using the MonthView controls. (If you use Calender Controls or
TextBoxes, the code would differ only slightly.) Put the following
code in the UserForm's code module.

Dim miEnd As Long

Private Sub CommandButton1_Click()
miEnd = Sheets("Sheet1").Range("A1").End(xlDown)
Sheets("Sheet1").Range("A1:A" & miEnd).AutoFilter _
Field:=1, Criteria1:="=" & MonthView1, _
Operator:=xlAnd, Criteria2:="<=" & MonthView2
'more code to copy filtered data elsewhere if desired
End Sub

Private Sub UserForm_Terminate()
'clear AutoFilter
Sheets("Sheet1").Range("A1:A" & miEnd).AutoFilter
End Sub

Hth,
Merjet


Hi, Dear Merjet,

I tested your code, it working Excellent, Thank you very much for your
support.

Last question is this how to copy the filtered data to the sheet2.

Thank you once agein. I am happy now.


with Best Regards. YOU ARE GREAT.

syed shahzad zafar
Madinah - KSA.

Date Range (Starting & Ending Date) - syed shahzad
 

Last question is this how to copy the filtered data to the sheet2.

Sheets("Sheet1").Range("A1:A" & miEnd).Copy _
Sheets("Sheet2").Range("A1")

Hth,
Merjet