Solved: Setting a date range

[lanhao]

Hi there,

I'm currently working on a project where I need set it where you pick two dates, and it will pull up all data within that range. Any suggestions as to how I would go about doing that? I already have the code necessary to pull the information and place it into the spreadsheet, I just have been stumped as to get the range set up. (This does need to be done in VBA, not as a cell formula unfortunately)

Thanks in advance for any help that can be provided.

[mvidas]

lanhao,

You can use inputboxes to get the dates from the user, and an if statement to help determine if it is in the range. You say you have the code to put the information into a spreadsheet, what exactly are you having trouble doing?
If you would just like some sample code to show how to compare:[vba]Sub lanhao()
Dim date1 As Date, date2 As Date, CheckDate As Date, tempStr As String
tempStr = InputBox("Please enter starting date", "Enter starting date")
If Not IsDate(tempStr) Then Exit Sub
date1 = DateValue(tempStr)
tempStr = InputBox("Please enter ending date", "Enter ending date")
If Not IsDate(tempStr) Then Exit Sub
date2 = DateValue(tempStr)
If date1 > date2 Then
MsgBox "Ending date is prior to starting date"
Exit Sub
End If
CheckDate = Range("A1").Value 'date to see if it is the range
If date1 <= CheckDate And CheckDate <= date2 Then
MsgBox "Check date is in range"
Else
MsgBox "Check date is not in range"
End If
End Sub[/vba]Matt

[lanhao]

well, what i need to do is have the program take a look at the information within the range of dates, specifically the days that people were out of work. This way it looks at a specific time period. So if I wanted to enter in the information for the time period of 6/1/2006 to 6/72006 I can pull up that information specifically.

I have put in below the base template of what i am going to be porting over information wise. [vba]Sub RetrieveDaily()
Set sht4 = Worksheets("Daily")
Set sht5 = Worksheets("Comments")
Dim i As Integer 'row counter for the data page "Comments" aids in finding the last row with info
Dim j As Integer 'row counter for the "FrontEnd" puts data in the correct row
i = 1
j = 6
' ClearAll Macro
' Macro recorded 5/10/2006 by Andy Lewis
'
Range("A6:H51").Select
Range("A6").Activate
Selection.ClearContents
Application.GoTo Reference:=Range("A6"), Scroll:=True

Do While IsEmpty(sht5.Cells(i, 1).Value) = False 'keep doing until we have searched through the whole data page "Comments"

If sht4.Range("A3") = sht5.Range("F" & i) Then 'this places the data if the names match
sht4.Range("C" & j) = sht5.Range("E" & i).Value
sht4.Range("B" & j) = sht5.Range("D" & i).Value
sht4.Range("A" & j) = sht5.Range("A" & i).Value
sht4.Range("D" & j) = sht5.Range("G" & i).Value
sht4.Range("E" & j) = sht5.Range("J" & i).Value
sht4.Range("F" & j) = sht5.Range("K" & i).Value
j = j + 1
End If
i = i + 1

Loop

End Sub
[/vba]

I also have a separate function that will get the start date and end date. (simply put in as StartDate and EndDate). That actually sets the dates in question.

[lanhao]

I got it to work finally with the help that was suggested, thank you so much

[mvidas]

Sorry about the delay in getting back to you, but it looks like you got it straightened out anyways! Glad I could help, let me know if you need anything else.
Matt

[lanhao]

hey, any help is good help. I really appreciate the feedback given, and well, it hepled me work through the issue. I am a 1 month old newbie to VBA, i started to teach myself it because it came up at work, and I thought some stuff would work a bit better from auserforms tandpoint, or as vba code instead of a spreadsheet filled with formulae.

Now I feel I can really cause my head to go intoa tailspin trying to figure out some of the newer stuff that I have been working on.