Populate cells with array

[zJenkins]

I have a macro that populates a range of cells with the months horizontally, but am getting an error when I try to make it vertical.

Horizontal:

ActiveCell.Resize(, 12) = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")

Vertical:

ActiveCell.Resize(12, ) = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")

It looks like it has something to do with the (12, )

Any help would be great. Thanks!

 

[Akuini]

Try:

ActiveCell.Resize(12) = Application.Transpose(Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"))

 

[zJenkins]

Nevermind, found a solution.

Thanks

ActiveCell.Resize(12, 1) = Application.Transpose(Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"))

 

[zJenkins]

Try:

ActiveCell.Resize(12) = Application.Transpose(Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"))

Thanks Akuini...didn't see that you had responded. I appreciate your time

 

[Rick Rothstein]

You cannot have a blank or zero in the Resize property... either put a 1 after the comma or omit the comma (doing that will make the columns argument the default of 1). Of course, after you fix that, your code will not place the month name abbreviation downward... you need to Transpose the array to place it vertically...

ActiveCell.Resize(12) = Application.Transpose(Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"))

There is a "simpler" code line that will do the same thing as the above...

ActiveCell.Resize(12) = [IF({1},TEXT(28*ROW(1:12),"mmm"))]

 

[footoo]

There is a "simpler" code line that will do the same thing as the above...

ActiveCell.Resize(12) = [IF({1},TEXT(28*ROW(1:12),"mmm"))]

How does it work?
(I note it doesn't work if over 12 rows.)

For a simple to understand code for any start month and any number of rows:

With ActiveCell
    .Value = "Jan"
    .AutoFill Destination:=.Resize(12)
End With

 

[footoo]

How does it work?
(I note it doesn't work if over 12 rows.)

OK. I worked it out.

 

[footoo]

This should work for any number of rows:

ActiveCell.Resize(12) = [TEXT(Date(1900,ROW(1:12),1),"mmm")]

 

[zJenkins]

There is a "simpler" code line that will do the same thing as the above...

ActiveCell.Resize(12) = [IF({1},TEXT(28*ROW(1:12),"mmm"))]

Thanks Rick, could you please explain this formula?

 

[zJenkins]

I understand the Text(28*row(1:12),"mmm", this is where the month text comes form, but how does the "If{1}" work?