Create variable month list in a column.

[mopey12345]

Hi everyone,
I have to create a list of months 6 (min) up to 12 (max) using any month as the start month. Two cells define the start month and the number of months. Is there a better way than creating 84 arrays to cover all instances? eg JANMONTHNAMES6 = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun""), upto JANMONTHNAMES12= Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"), for each month. Thanks in advance.

 

[Toadstool]

Hi Mopey12345,

You've asked under Excel questions but I'm not aware of the Array() function so hope this helps:

Mopey12345.xlsx
	A	B	C
1	Start Month	Number of Months	List
2	Jun	9	Jun
3			Jul
4			Aug
5			Sep
6			Oct
7			Nov
8			Dec
9			Jan
10			Feb
11
12
13
Sheet1

Cell Formulas
Range	Formula
C2:C13	C2	=IF(ROW()-ROW($C$1)>$B$2,"",TEXT(DATE(2000,MATCH($A$2,{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},0)+ROW()-ROW($C$1)-1,1),"mmm"))

 

[jasonb75]

Why not just use a single array for all 12 months then use the month in the cell as a starting point to loop part of the array?

If you need it to cross the year end from Dec to Jan then use a 24 month array instead.

@Toadstool it's a vba question, not formula.

 

[mopey12345]

Hi Mopey12345,

You've asked under Excel questions but I'm not aware of the Array() function so hope this helps:

Mopey12345.xlsx
	A	B	C
1	Start Month	Number of Months	List
2	Jun	9	Jun
3			Jul
4			Aug
5			Sep
6			Oct
7			Nov
8			Dec
9			Jan
10			Feb
11
12
13
Sheet1

Cell Formulas
Range	Formula
C2:C13	C2	=IF(ROW()-ROW($C$1)>$B$2,"",TEXT(DATE(2000,MATCH($A$2,{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},0)+ROW()-ROW($C$1)-1,1),"mmm"))

Thank you.

 

[mopey12345]

Why not just use a single array for all 12 months then use the month in the cell as a starting point to loop part of the array?

If you need it to cross the year end from Dec to Jan then use a 24 month array instead.

@Toadstool it's a vba question, not formula.

Thank you.

 

[jasonb75]

There is no ideal answer to your question without knowing what the next steps are in the task. What will work for one thing might not always be as good for another.

If you decide to go with the formula based option that @Toadstool suggested then here are a couple of shorter alternatives. Note that neither of these will work if the date range rolls from December back to January. The formula in column E is for office 365 only but the one in column D will work with old versions as well.

Book1
	A	B	C	D	E
2	Feb	9		Feb	Feb
3				Mar	Mar
4				Apr	Apr
5				May	May
6				Jun	Jun
7				Jul	Jul
8				Aug	Aug
9				Sep	Sep
10				Oct	Oct
Sheet3

Cell Formulas
Range	Formula
E2:E10	E2	=TEXT(SEQUENCE(B2,,MONTH(A2&"0"))*29,"mmm")
D2:D10	D2	=IF(ROWS(D$2:D2)>B$2,"",TEXT((MONTH(A$2&"0")+ROWS(D$2:D2)-1)*29,"mmm"))
Dynamic array formulas.

 

[mopey12345]

There is no ideal answer to your question without knowing what the next steps are in the task. What will work for one thing might not always be as good for another.

If you decide to go with the formula based option that @Toadstool suggested then here are a couple of shorter alternatives. Note that neither of these will work if the date range rolls from December back to January. The formula in column E is for office 365 only but the one in column D will work with old versions as well.

Book1
	A	B	C	D	E
2	Feb	9		Feb	Feb
3				Mar	Mar
4				Apr	Apr
5				May	May
6				Jun	Jun
7				Jul	Jul
8				Aug	Aug
9				Sep	Sep
10				Oct	Oct
Sheet3

Cell Formulas
Range	Formula
E2:E10	E2	=TEXT(SEQUENCE(B2,,MONTH(A2&"0"))*29,"mmm")
D2:D10	D2	=IF(ROWS(D$2:D2)>B$2,"",TEXT((MONTH(A$2&"0")+ROWS(D$2:D2)-1)*29,"mmm"))
Dynamic array formulas.

Thanks. The formula solution(s) work great to get us up an running but I am trying to learn vba, so was attempting to do it that way. Regards Phil

 

[jasonb75]

There are probably more ways to do it with vba than there are with formulas. With vba the best method often depends what else you're going to do with the array afterwards.

If you're going to loop through it then a single fixed array with a defined start and end point would be the easiest way, if you want the array to only contain the required elements then you could look at writing the array on the fly. Not something that I use a lot of so I can never remember the correct code to use, if you wanted to look into it ReDim Preserve is the key to it.

 

[mopey12345]

There are probably more ways to do it with vba than there are with formulas. With vba the best method often depends what else you're going to do with the array afterwards.

If you're going to loop through it then a single fixed array with a defined start and end point would be the easiest way, if you want the array to only contain the required elements then you could look at writing the array on the fly. Not something that I use a lot of so I can never remember the correct code to use, if you wanted to look into it ReDim Preserve is the key to it.

Thanks. At my level the single fixed array with a defined start and end point would be way I will try. Thanks again for the help. Appreciated.

 

[mopey12345]

I've got this but seems a lot of code.
If Worksheets("Sheet1").Range("C3") = "Jan" And Worksheets("Sheet1").Range("C4") = "6" Then
Worksheets("Sheet1").Range("D3") = "Jan"
Worksheets("Sheet1").Range("D4") = "Feb"
Worksheets("Sheet1").Range("D5") = "Mar"
Worksheets("Sheet1").Range("D6") = "Apr"
Worksheets("Sheet1").Range("D7") = "May"
Worksheets("Sheet1").Range("D8") = "Jun"
ElseIf Worksheets("Sheet1").Range("C3") = "Jan" And Worksheets("Sheet1").Range("C4") = "7" Then
Worksheets("Sheet1").Range("D3") = "Jan"
Worksheets("Sheet1").Range("D4") = "Feb"
Worksheets("Sheet1").Range("D5") = "Mar"
Worksheets("Sheet1").Range("D6") = "Apr"
Worksheets("Sheet1").Range("D7") = "May"
Worksheets("Sheet1").Range("D8") = "Jun"
Worksheets("Sheet1").Range("D9") = "Jul"
ElseIf Worksheets("Sheet1").Range("C3") = "Jan" And Worksheets("Sheet1").Range("C4") = "8" Then
Worksheets("Sheet1").Range("D3") = "Jan"
Worksheets("Sheet1").Range("D4") = "Feb"
Worksheets("Sheet1").Range("D5") = "Mar"
Worksheets("Sheet1").Range("D6") = "Apr"
Worksheets("Sheet1").Range("D7") = "May"
Worksheets("Sheet1").Range("D8") = "Jun"
Worksheets("Sheet1").Range("D9") = "Jul"
Worksheets("Sheet1").Range("D10") = "Aug"
ElseIf Worksheets("Sheet1").Range("C3") = "Jan" And Worksheets("Sheet1").Range("C4") = "9" Then
Worksheets("Sheet1").Range("D3") = "Jan"
Worksheets("Sheet1").Range("D4") = "Feb"
Worksheets("Sheet1").Range("D5") = "Mar"
Worksheets("Sheet1").Range("D6") = "Apr"
Worksheets("Sheet1").Range("D7") = "May"
Worksheets("Sheet1").Range("D8") = "Jun"
Worksheets("Sheet1").Range("D9") = "Jul"
Worksheets("Sheet1").Range("D10") = "Aug"
Worksheets("Sheet1").Range("D11") = "Sep"
ElseIf Worksheets("Sheet1").Range("C3") = "Jan" And Worksheets("Sheet1").Range("C4") = "10" Then
Worksheets("Sheet1").Range("D3") = "Jan"
Worksheets("Sheet1").Range("D4") = "Feb"
Worksheets("Sheet1").Range("D5") = "Mar"
Worksheets("Sheet1").Range("D6") = "Apr"
Worksheets("Sheet1").Range("D7") = "May"
Worksheets("Sheet1").Range("D8") = "Jun"
Worksheets("Sheet1").Range("D9") = "Jul"
Worksheets("Sheet1").Range("D10") = "Aug"
Worksheets("Sheet1").Range("D11") = "Sep"
Worksheets("Sheet1").Range("D12") = "Oct"
ElseIf Worksheets("Sheet1").Range("C3") = "Jan" And Worksheets("Sheet1").Range("C4") = "11" Then
Worksheets("Sheet1").Range("D3") = "Jan"
Worksheets("Sheet1").Range("D4") = "Feb"
Worksheets("Sheet1").Range("D5") = "Mar"
Worksheets("Sheet1").Range("D6") = "Apr"
Worksheets("Sheet1").Range("D7") = "May"
Worksheets("Sheet1").Range("D8") = "Jun"
Worksheets("Sheet1").Range("D9") = "Jul"
Worksheets("Sheet1").Range("D10") = "Aug"
Worksheets("Sheet1").Range("D11") = "Sep"
Worksheets("Sheet1").Range("D12") = "Oct"
Worksheets("Sheet1").Range("D13") = "Nov"
ElseIf Worksheets("Sheet1").Range("C3") = "Jan" And Worksheets("Sheet1").Range("C4") = "12" Then
Worksheets("Sheet1").Range("D3") = "Jan"
Worksheets("Sheet1").Range("D4") = "Feb"
Worksheets("Sheet1").Range("D5") = "Mar"
Worksheets("Sheet1").Range("D6") = "Apr"
Worksheets("Sheet1").Range("D7") = "May"
Worksheets("Sheet1").Range("D8") = "Jun"
Worksheets("Sheet1").Range("D9") = "Jul"
Worksheets("Sheet1").Range("D10") = "Aug"
Worksheets("Sheet1").Range("D11") = "Sep"
Worksheets("Sheet1").Range("D12") = "Oct"
Worksheets("Sheet1").Range("D13") = "Nov"
Worksheets("Sheet1").Range("D14") = "Dec"

Need to replicate this for all the other months.