date comparison problems
 

Your solution does work well for the problem I described.

As I said in my last reply, however, I would like to be able to specify a
date range other than the entire year (or greater than one year). This would
mean having two textboxes to specify dates and then making a comparing the
data to these dates to include them or not. So, here's where I stand.

If I want the entire year, I can use your solution. As I have it,
specifying the date range goes like this:
dim date1 as date
dim date2 as date

If optionbutton1 = true then
'your code
Else
If IsDate(textbox1) then
date1 = dateserial(year(textbox1),month(textbox1),day(text box1))
End if

If IsDate(textbox2) then
date2 = dateserial(year(textbox2),month(textbox2),day(text box2))
End If
End If

dim sh as worksheet
dim c as variant
dim myrange as range
For Each sh In ThisWorkbook.Worksheets
With Sheets(sh.Name)
Set myrange = .Range("A1", "A365")
End With
For Each c In myrange
Select Case cells(c.row,1) 'date of transaction is in column one
Case date1 to date2
'code to export to array, might as well be _
'a messagebox displaying cells(c.row,1) for debugging
purposes
End select
Next c
Next sh

"Ardus Petus" wrote:

Sub test()
Dim sh As Worksheet
Dim c As Range
Dim myYear As Integer

myYear = Combobox1.Value
For Each sh In ThisWorkbook.Worksheets
For Each c In sh.Range("A1:A366")
If Year(c.Value) = myYear Then
MsgBox "Cell: " & c.Address & _
"=" & Format(c.Value, "dd/mm/yyyy")
End If
Next c
Next sh
End Sub


HTH
--
AP

"drhalter" a écrit dans le message de
...
Hi, I'm using a form to define a date range within which VB will extract
data
of transactions (like in a checkbook) from my workbook. The data is then
put
into a new file. The transactions range in date from 9/2004 to 4/2006.
There are several sheets (my test model contains only two). When I ask
the
program to give me all the transactions from 2005, I get all the data from
sheet1 in 2005 plus one in 2006 (and it is always the 3/18/2006, and it is
surrounded by many others from 2006), I get the first half of 2005 from
sheet2. This is when sheet1 is activated at runtime. If sheet2 is
activated
at runtime, I don't get the data from 2006, but the rest of the data is
approximately the same. The problem data from 2006 is from sheet1.

Here is a sample of my code (a bit distilled).
sheets(1).activate 'if I vary the integer in this line, the results
are
different

dim date1 as date
dim date2 as date
date1 = dateserial(combobox1,1,1)
date2 = dateserial(combobox1,12,31)

dim sh as worksheet
dim c as variant
dim myrange as range
For Each sh In ThisWorkbook.Worksheets
With Sheets(sh.Name)
Set myrange = .Range("A1", "A365")
End With
For Each c In myrange
Select Case cells(c.row,1) 'date of transaction is in column
one
Case date1 to date2
'code to export to array, might as well be _
'a messagebox displaying cells(c.row,1) for debugging
purposes
End select
Next c
Next sh

I hope someone has an idea, I can't think of why this isn't behaving
properly. I've tried retyping the raw data in the workbook. I've also
tried:
dim testdate as date
testdate =
dateserial(year(cells(c.row,1)),month(cells(c.row, 1)),day(cells(c.row,1)))
and then using testdate in the Select statement instead of cells(c.row,1).

I get similar problems with 2004. I don't seem to have trouble with 2006,
though I suppose if I added some 2007 transactions I could have the same
difficulty when attempting to use 2006.

Any ideas on where to go from here?
Thanks,
Dave