Apollo11
05-11-2020, 02:57 AM
Hi, I'm new to this forum and a relative newbie to VBA so please treat me gently. :hi:
I have adapted a piece of VBA code for use in one of my spreadsheets and have had problems with comparing "dates". Perhaps unusually, I seem to have found a solution already, but I really want to understand what was wrong with my original code and why it didn't work properly. Basically, my code looks through a worksheet and archives to another worksheet any rows for which the date in column Y is greater than 1 year ago (as of current date). Before executing the actual "archive" routine, I use a CountIf function and display a message box, informing the user how many records will be archived. Below is the code used to calculate the latter.
Dim xRg As Range
Dim LTRows As Long
Dim L As Long
LTRows = Worksheets("LIVE TRACKER").UsedRange.Rows.Count
Set xRg = Worksheets("LIVE TRACKER").Range("Y6:Y" & LTRows)
L = Application.WorksheetFunction.CountIf(xRg, "<" & Date - 365)
The variable "L" is then used in my message box to advise how many records will be archived. This seemed to be working fine until, by chance, I happened to spot yesterday (10/05/2020 - UK format) that it started reporting that "1 record will be archived" even though I knew that there were no records more than one year old in the worksheet. I tracked it down to one particular record which contained the date 26/09/2019 (UK format) - clearly this is not greater than one year old. I tried a number of different values in this cell and eventually found that the message changed to "no records" when I used the date 05/11/2019 (still UK format). It is surely no coincidence that, if this were in US format, it would translate as 11/05/2019 and therefore just one day short of a year ago. To cure the problem, I have created a variable "CutOff" which is type=double and given it the value = (Date - 365). Then I use CountIf < CutOff instead of CountIf < Date-365. The revised code is as follows:
Dim xRg As Range
Dim LTRows As Long
Dim L As Long
Dim CutOff As Double
LTRows = Worksheets("LIVE TRACKER").UsedRange.Rows.Count
Set xRg = Worksheets("LIVE TRACKER").Range("Y6:Y" & LTRows)
CutOff = CDbl(Date - 365)
L = Application.WorksheetFunction.CountIf(xRg, "<" & CutOff)
What I am struggling to understand is why VBA would treat this differently. I understand that if I were to reference an explicit date (eg "01/05/2020") VBA would assume this to be US format, but I am referencing the system Date which I would expect to be in the form of a serial number and therefore independent from any regional formatting applied to the worksheet.
It's worth mentioning that the bit of code used to actually archive the data seems to work perfectly, even though it effectively uses the same criteria as my original code, eg. If (xRg(K).Value) < (Date - 365)
There is clearly a subtle difference, but I'm failing to see it. Any advice or guidance would be gratefully received.
Thanks in advance
Mike
I have adapted a piece of VBA code for use in one of my spreadsheets and have had problems with comparing "dates". Perhaps unusually, I seem to have found a solution already, but I really want to understand what was wrong with my original code and why it didn't work properly. Basically, my code looks through a worksheet and archives to another worksheet any rows for which the date in column Y is greater than 1 year ago (as of current date). Before executing the actual "archive" routine, I use a CountIf function and display a message box, informing the user how many records will be archived. Below is the code used to calculate the latter.
Dim xRg As Range
Dim LTRows As Long
Dim L As Long
LTRows = Worksheets("LIVE TRACKER").UsedRange.Rows.Count
Set xRg = Worksheets("LIVE TRACKER").Range("Y6:Y" & LTRows)
L = Application.WorksheetFunction.CountIf(xRg, "<" & Date - 365)
The variable "L" is then used in my message box to advise how many records will be archived. This seemed to be working fine until, by chance, I happened to spot yesterday (10/05/2020 - UK format) that it started reporting that "1 record will be archived" even though I knew that there were no records more than one year old in the worksheet. I tracked it down to one particular record which contained the date 26/09/2019 (UK format) - clearly this is not greater than one year old. I tried a number of different values in this cell and eventually found that the message changed to "no records" when I used the date 05/11/2019 (still UK format). It is surely no coincidence that, if this were in US format, it would translate as 11/05/2019 and therefore just one day short of a year ago. To cure the problem, I have created a variable "CutOff" which is type=double and given it the value = (Date - 365). Then I use CountIf < CutOff instead of CountIf < Date-365. The revised code is as follows:
Dim xRg As Range
Dim LTRows As Long
Dim L As Long
Dim CutOff As Double
LTRows = Worksheets("LIVE TRACKER").UsedRange.Rows.Count
Set xRg = Worksheets("LIVE TRACKER").Range("Y6:Y" & LTRows)
CutOff = CDbl(Date - 365)
L = Application.WorksheetFunction.CountIf(xRg, "<" & CutOff)
What I am struggling to understand is why VBA would treat this differently. I understand that if I were to reference an explicit date (eg "01/05/2020") VBA would assume this to be US format, but I am referencing the system Date which I would expect to be in the form of a serial number and therefore independent from any regional formatting applied to the worksheet.
It's worth mentioning that the bit of code used to actually archive the data seems to work perfectly, even though it effectively uses the same criteria as my original code, eg. If (xRg(K).Value) < (Date - 365)
There is clearly a subtle difference, but I'm failing to see it. Any advice or guidance would be gratefully received.
Thanks in advance
Mike