Load array with dates following user input

[seanpears99]

Hi

I have a column of months and years

January 2012
February 2012
March 2012
April 2012
May 2012

I ask the user to select a start date and end date and need to build an array of the start and end dates and the ones which fall in between, i.e. if they select February 2012 and May 2012, I need to load, February to May 2012 into an array for sheet selection processing.

I am not the most proficient of coders, this is a hobby, so currently I was looping (do while) down the list until I hit the start date and then looping from there to the end date and loading the array in between.

Is this best way to do this?

Thanks

Sean

 

[JoeMo]

Here's some code you can adapt to your needs. It allows the user to select the start and end dates with the mouse and then loads an array with the dates from start to end inclusive.

Sub DatesToArray()
Dim aDates(), sDate As Range, eDate As Range, Rws As Long

Set sDate = Application.InputBox("Select a start date", Type:=8)
If sDate Is Nothing Then Exit Sub
Set eDate = Application.InputBox("Select an end date", Type:=8)
If eDate Is Nothing Then Exit Sub
Rws = Range(sDate, eDate).Rows.Count
ReDim aDates(1 To Rws)
For i = 1 To Rws
    aDates(i) = sDate.Offset(i - 1).Value
Next i
End Sub

 

[AlphaFrog]

If you have two dates, you can create a loop to advance a result date for each month between the two dates inclusive.

Example:

[color=darkblue]Sub[/color] test()


    [color=darkblue]Dim[/color] dMonthly [color=darkblue]As[/color] [color=darkblue]Date[/color]
    [color=darkblue]Dim[/color] strStart [color=darkblue]As[/color] [color=darkblue]String[/color], strEnd [color=darkblue]As[/color] [color=darkblue]String[/color]
    
    strStart = "February 2012"
    strEnd = "May 2012"
    
    dMonthly = DateValue("1-" & strStart)
    
    [color=darkblue]Do[/color] [color=darkblue]Until[/color] dMonthly > DateValue("1-" & strEnd)
        MsgBox Format(dMonthly, "mmmm yyyy")   [color=green]'Example output[/color]
        dMonthly = DateAdd("m", 1, dMonthly)
    [color=darkblue]Loop[/color]
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[seanpears99]

Thanks Jo

My code was as follows, dont laugh, I loop as I dont know what else to do

Do Until Sheets("Working Days").Range("N" & z).Value = startmonth

z = z + 1
Loop

Do Until Sheets("Working Days").Range("N" & z).Value = endmonth

nums(i) = Sheets("Working Days").Range("N" & z).Value

z = z + 1
i = i + 1
ReDim nums(i)
Loop

I am dropping out when I find the end date too early

 

[Rick Rothstein]

Using JoeMo's code a model...

Sub DatesToArray()
Dim aDates(), sDate As Range, eDate As Range, Rws As Long

    Set sDate = Application.InputBox("Select a start date", Type:=8)
    If sDate Is Nothing Then Exit Sub
    Set eDate = Application.InputBox("Select an end date", Type:=8)
    If eDate Is Nothing Then Exit Sub

    Rws = Range(sDate, eDate).Rows.Count
    ReDim aDates(1 To Rws)
    For i = 1 To Rws
        aDates(i) = sDate.Offset(i - 1).Value
    Next i
End Sub

we can eliminate all the code highlighted in red and replace it with this single line of code (thus completely eliminating the loop)...

aDates = WorksheetFunction.Transpose(Range(sDate.Address & ":" & eDate.Address).Value)

 

[seanpears99]

Thanks for your time guys, you are all on a different planet when it comes to this stuff.

AlphaFrogs code worked like a charm. (see below)

Cheers
Sean


dMonthly = DateValue("1-" & startmonth)

Do Until dMonthly > DateValue("1-" & endmonth)

dMonthly = DateAdd("m", 1, dMonthly)
nums(i) = Format(dMonthly, "mmmm yyyy")
i = i + 1
ReDim Preserve nums(i)
Loop

 

[AlphaFrog]

Thanks for your time guys, you are all on a different planet when it comes to this stuff.

AlphaFrogs code worked like a charm. (see below)

Cheers
Sean


dMonthly = DateValue("1-" & startmonth)

Do Until dMonthly > DateValue("1-" & endmonth)

dMonthly = DateAdd("m", 1, dMonthly)
nums(i) = Format(dMonthly, "mmmm yyyy")
i = i + 1
ReDim Preserve nums(i)
Loop

You are adding one month to the startmonth before you put it in the array. So the startmonth is not included. Swap the order of these two lines.

dMonthly = DateAdd("m", 1, dMonthly)
nums(i) = Format(dMonthly, "mmmm yyyy")