Dates in range not selecting properly

[Guest]

I want to select lines containing a range of dates from a series of
worksheets and to paste these lines into another "Results" worksheet. The
sub works well, except that the date selection picks dates outside my range.
With datestart = 1 Jan 2006, and dateend = 31 Dec 2006, the following
program picks all dates ('datews') within this range, but also gets some
outside it.

'Dim datestart As Date, dateend As Date, datews As Date
'If datews >= datestart And datews <= dateend Then
'.....
'End if

Any ideas?
Thanks

 

[Ardus Petus]

The code you post is correct (works by me).

The error might reside in some other part of your code.

Why not post your whole code (if not too big)

HTH

 

[Guest]

And, additionally, make sure that the dates you're operating on are really
regarded as dates by Excel, and that they are properly parsed wrt regional
settings.

Why break and single-step your code to check exactly what your code does,
and what values are stored in the variables (datews, datestart, and dateend).

Cheers,
/MP

 

[Guest]

Ardus Petus,

The code is pasted below. It looks alright to me, but it still selects
dates outside my range.

Any further help would be appreciated.
Thanks


' Macro recorded 10/05/2006 by Ray
'
' Keyboard Shortcut: Ctrl+f
' To cover all worksheets from "start" to "finish"

Dim lastrowdiv As Long, lastroww As Long, jrow As Long, datestart As
Date, dateend As Date, datew As Date, ws As Worksheet
Sheets("div05-06").Select
datestart = Range("A1").Value
dateend = Range("B1").Value
With ActiveSheet
lastrowdiv = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
End With
If lastrowdiv = 2 Then
lastrowdiv = 3
End If
For jws = Worksheets("Start").Index To Worksheets("Finish").Index
Set ws = Worksheets(jws)
ws.Activate
With ActiveSheet
lastroww = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
For jrow = 1 To lastroww Step 1
datew = Range("A" & jrow).Value
If datew >= datestart And datew <= dateend Then
ws.Activate
With ActiveSheet
Rows(jrow).Copy
End With
Sheets("div05-06").Select
With ActiveSheet
.Rows(lastrowdiv).Select
ActiveSheet.Paste
lastrowdiv = lastrowdiv + 1
End With
End If
Next jrow
Next jws
End Sub

 

[Ardus Petus]

The code looks fine (though you could avoid selecting worksheets).
Now you must debug your code:
put a breakpoint on offending line
Run macro.
When it hits the breakpoint, check the values of your variables

HTH

 

[Guest]

Apologies to all. I had assumed, naively, that the error was in the date
format, whereas, of course, the error was in my code. If the line:
ws.activate '(line 20 of my original code)
is moved up two lines so that the code now reads:

.......
For jrow = 1 To lastroww Step 1
ws.activate
datew = Range("A" & jrow).Value
If datew >= datestart And datew <= dateend Then
With ActiveSheet
Rows(jrow).Copy
......

the code ensures that I select the date from the same worksheet from which I
subsequently copy the row. The old code might, after I had looped, select
a date from the correct row ('jrow') but from a different sheet from the
subsequently copied line.

Thanks to everyone for their help, and my apologies for wasting their time.