Do While Loop with Dates 2

[Knicks]

I am having trouble looping through a date set. Is this something that is tricky? The main issue is I can't get the simplest portion which seems odd - basically setting the begin date variable.

Here is my code any help would be appreciated!! I've tried this as a date variable and variant.


Dim VarDate As Date
Dim enddate As Date

vardate = #12/31/2010#
enddate = #1/1/2011#

Do While VarDate < enddate
VarDate = DateAdd("D", 1, VarDate)
debug.print Vardate

Loop



Right now the code is not supposed to do more than simply loop through from 12/31/2010 until it reaches the enddate at 12/31/2011. I have no problems with the enddate, but the begin date starts in June for some odd reason. We use a Citrix system here, no PCs...I am wondering if it has something to do with that. Or is looping through dates something that requires extra care?

 

[SkipVought]

Hi,

Change your assignments...

vardate = DateSerial(2010,12,31)
enddate = DateSerial(2011,1,1)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Andrzejek]

I would go with Skip's sollution, but you can to that, too:

Dim VarDate As Date
Dim enddate As Date
[blue]
VarDate = CDate(Format("12/31/2010", "mm/dd/yyyy"))
enddate = CDate(Format("1/1/2011", "mm/dd/yyyy"))
[/blue]
Do While VarDate < enddate
    VarDate = DateAdd("d", 1, VarDate)
    Debug.Print VarDate
Loop

Have fun.

---- Andy

 

[MajP]

What you are doing works most of the time (especially if you are on US system settings). But it is a regional setting issue. I assume you are using non-us settings? It appears to see the literal date as 31/12/2010. Which would be in July of 2012. The posted solutions should overcome the regional setting issues.

 

[Knicks]

VarDate = CDate(Format("12/31/2010", "mm/dd/yyyy"))
enddate = CDate(Format("1/1/2011", "mm/dd/yyyy"))

Could my issue be with the immediate window not capable of showing 200+ rows.....because if I put in the very limited range of above it works fine (it always new where to end) but if I change the end to 2012 it only shows from June in the immediate window. I think it is erasing the correct results. If that is the case then thats fine as the Immediate window is only for testing. Ultimately I am going to populate with working days m-f for the year.

 

[SkipVought]

12/31/2010 to 1/1/2011 is ONE DAY!

Yes, only about the last 200 writes. Easy enough to check out.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Knicks]

Ahh,

I new working with dates shouldn't be that hard. The immediate window was tripping me up.

Thanx for the help!