date comparison problems

[Guest]

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

 

[Ardus Petus]

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

 

[Guest]

I'm not at my own computer now, but I'll give your solution a try later
today.

I should note that I had been using the select case date1 to date2 method
because my form allowed two methods of date range entry. Which one is used
is controlled by optionbuttons and an if statement in the code. The
simplest, and probably most used method would be to go by the year, as I
presented and you modified below. The other method was to allow a specific
date range, like 7/1/05 to 12/31/05. When the user chooses this method, the
textbox entries are changed to date variables:
If IsDate(textbox1) Then
date1 = Dateserial(year(textbox1),month(textbox1),day(textbox1))
End If
....and the same for date2 and textbox2, the code I presented previously
would then work nicely by either specifying the year or a date range.
I try to simplify the problems I'm having when I present something to the
discussion group. I should say now that I had similar problems with using
7/1/05 to 12/31/05 as well.

Again, I'll try this method and reply later today or tomorrow when I've had
a chance to try it.

 

[Guest]

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(textbox1))
End if

If IsDate(textbox2) then
date2 = dateserial(year(textbox2),month(textbox2),day(textbox2))
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

 

[Guest]

Don't ask me how or why, but when I transferred what was basically my
original code from my test workbook into my working workbook, the code
actually started behaving as expected. I'm still at a loss as to what
happened, but at least it is working again.

Dave