Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
date comparison problems
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 "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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF(AND with date comparison | Excel Worksheet Functions | |||
date comparison | Excel Discussion (Misc queries) | |||
date comparison problems | Excel Programming | |||
date comparison problems | Excel Programming | |||
Date comparison | Excel Worksheet Functions |