EXCEL VBA - Do While loop with 2 dates

Question

Working on populating a row in excel with dates between a start date and current date. The population is weekly and below is the function I have made. It works fine up until the point where it doesn't stop but continues to go infinitely until there is an overflow error hence my assumption is that CurrentDate is not working properly.

The 2 dates used are StartDate = 04/1/2016 and CurrentDate = 12/07/2017.

Any help or suggestions would be greatly appreciated.

Public Function PopulateStartOfWeekDates()

    Dim wsCRC As Worksheet
    Set wsCRC = Worksheets("CRC")

    Dim StartDate As Date
    Dim CurrentDate As Date
    StartDate = FirstMondayOfYear()
    CurrentDate = Date

    Dim WeekOffset As Integer
    Dim i As Integer
    i = 12
    WeekOffset = 0

    Debug.Print StartDate
    Debug.Print CurrentDate

    Do While StartDate < CurrentDate        
        wsCRC.Cells(5, i) = StartDate + WeekOffset
        wsCRC.Cells(5, i).EntireColumn.AutoFit
        i = i + 1
        WeekOffset = WeekOffset + 7                    
    Loop

End Function

you need to share your FirstMondayOfYear function, what value are you getting for StartDate when running in debug mode ? — Shai Rado
– Shai Rado, Commented Jul 12, 2017 at 8:01
StartDate remains the same in the loop, unless im mistaken? — Nathan_Sav
– Nathan_Sav, Commented Jul 12, 2017 at 8:03
Also, where do you increment the value of StartDate inside the loop Do While StartDate < CurrentDate ? don't you mean to add at the end StartDate = StartDate +7 ? — Shai Rado
– Shai Rado, Commented Jul 12, 2017 at 8:03

YowE3K · Accepted Answer · 2017-07-12 08:24:31Z

2

If you decide you need to maintain the value of StartDate (e.g. to use later in the code), you could replace your loop with:

i = 0
Do While StartDate + i * 7 < CurrentDate
    wsCRC.Cells(5, i + 12) = StartDate + i * 7
    wsCRC.Cells(5, i + 12).EntireColumn.AutoFit
    i = i + 1
Loop

answered Jul 12, 2017 at 8:24

YowE3K

24k7 gold badges28 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

SBozhko Over a year ago

That' a good idea ill keep this in mind as it definitely looks useful, but I use another function to get the start date so I don't particularly need to implement this right now

SBozhko · Accepted Answer · 2017-07-12 08:07:47Z

0

After looking at this myself I realized I wasn't increasing the startdate hence the loop was infinite. Thanks to @Nathan_Sav for pointing this out in the comments too.

answered Jul 12, 2017 at 8:07

SBozhko

3151 gold badge6 silver badges21 bronze badges

1 Comment

Nathan_Sav Over a year ago

I always add a break point at things like wend and loop when working, just in case :)

Collectives™ on Stack Overflow

EXCEL VBA - Do While loop with 2 dates

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related