searching for a range of dates
 

Hello

I have a problem. I have a list of dates in column 'A' and need to
find and display all the dates within a range, say all date from
01/03/2004 to 08/03/2004. the following code works, but it does not
when I try and have the user enter the dates from a user form.

Sub test()
startdate = "03/03/2004"
enddate = "08/03/2004"
Worksheets("sheet1").Activate
For i = 1 To 19
If (startdate <= Worksheets("sheet1").Cells(i, 1).Text) And _
(enddate = Worksheets("sheet1").Cells(i, 1).Text) Then
'go here
Debug.Print Worksheets("sheet1").Cells(i, 1).Text

Else
' go here

End If
Next i


End Sub


the actual code in the workbook look like this, any help would be
appreciated.

UserForm3.Show ' user form to get start and end date range

'wkrpstartdate this is the start date
'wkrpenddate this is the end date
Workbooks("sdrdata.xls").Worksheets("tempreport"). Activate

Workbooks("sdrdata.xls").Worksheets("tempreport"). Cells(1, 1) =
"Servers Installed on dates " + CStr(wkrpstartdate) + " To " +
CStr(wkrpenddate)

Workbooks("sdrdata.xls").Worksheets("Serverlist"). Activate
LastRowServer = Cells.SpecialCells(xlLastCell).Row

For RowP = 2 To LastRowServer
If (<= CStr(wkrpstartdate) <=
CStr(Workbooks("sdrdata.xls").Worksheets("serverli st").Cells(RowP,
10))) And (CStr(wkrpenddate) =
CStr(Workbooks("sdrdata.xls").Worksheets("serverli st").Cells(RowP,
10))) Then
' select row and past to tempreport sheet
ActiveCell.EntireRow.Select
Selection.Copy
Workbooks("sdrdata.xls").Worksheets("tempreport"). Activate
LastRowtempreport = Cells.SpecialCells(xlLastCell).Row
Cells(LastRowtempreport + 1, 1).Activate
ActiveSheet.Paste
Workbooks("sdrdata.xls").Worksheets("serverlist"). Activate
End If

Next RowP

searching for a range of dates
 

Try this

Sub test()
startdate = CDate(Textbox1.Text)
enddate = CDate(Textbox2.Text)
Worksheets("sheet1").Activate
For i = 1 To 19
If (startdate <= Worksheets("sheet1").Cells(i, 1).Text) And _
(enddate = Worksheets("sheet1").Cells(i, 1).Text) Then
'go here
Debug.Print Worksheets("sheet1").Cells(i, 1).Text

Else
' go here

End If
Next i


End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"stu H" wrote in message
om...
Hello

I have a problem. I have a list of dates in column 'A' and need to
find and display all the dates within a range, say all date from
01/03/2004 to 08/03/2004. the following code works, but it does not
when I try and have the user enter the dates from a user form.

Sub test()
startdate = "03/03/2004"
enddate = "08/03/2004"
Worksheets("sheet1").Activate
For i = 1 To 19
If (startdate <= Worksheets("sheet1").Cells(i, 1).Text) And _
(enddate = Worksheets("sheet1").Cells(i, 1).Text) Then
'go here
Debug.Print Worksheets("sheet1").Cells(i, 1).Text

Else
' go here

End If
Next i


End Sub


the actual code in the workbook look like this, any help would be
appreciated.

UserForm3.Show ' user form to get start and end date range

'wkrpstartdate this is the start date
'wkrpenddate this is the end date
Workbooks("sdrdata.xls").Worksheets("tempreport"). Activate

Workbooks("sdrdata.xls").Worksheets("tempreport"). Cells(1, 1) =
"Servers Installed on dates " + CStr(wkrpstartdate) + " To " +
CStr(wkrpenddate)

Workbooks("sdrdata.xls").Worksheets("Serverlist"). Activate
LastRowServer = Cells.SpecialCells(xlLastCell).Row

For RowP = 2 To LastRowServer
If (<= CStr(wkrpstartdate) <=
CStr(Workbooks("sdrdata.xls").Worksheets("serverli st").Cells(RowP,
10))) And (CStr(wkrpenddate) =
CStr(Workbooks("sdrdata.xls").Worksheets("serverli st").Cells(RowP,
10))) Then
' select row and past to tempreport sheet
ActiveCell.EntireRow.Select
Selection.Copy
Workbooks("sdrdata.xls").Worksheets("tempreport"). Activate
LastRowtempreport = Cells.SpecialCells(xlLastCell).Row
Cells(LastRowtempreport + 1, 1).Activate
ActiveSheet.Paste
Workbooks("sdrdata.xls").Worksheets("serverlist"). Activate
End If

Next RowP