Array Variable for Month Names

[Scott]

I have a sub below called CreateMonthValues() that I'm simply trying to
insert the 12 month names into range (B1:B12). I'm trying to use an array to
hold the month names, but I get an error saying "can't assign value to
array", on the line below that begins "arryMonthList = Array("January",
"Febuary", etc.).

I'm not real good with arrays and was hoping someone could help me modify my
sub to populate the range (B1:B12) with the 12 month names.

Any help?

CODE ******************************************

Sub CreateMonthValues()

Dim arryMonthList(1 To 12) As String
arryMonthList = Array("January", "Febuary", "March", "April", "May",
"June", "July", "August", "September", "October", "November", "December")

Dim c As Range
Sheets("DateHelper").Select
Range("B1").Select

'set a range variable equal to the first data cell in column B
Set c = ActiveSheet.Range("B1")

Dim iCount As Integer
Dim Max As Integer
Max = 12 ' maximum array size
ReDim MyNames(1 To Max) ' declares the array variable with the necessary
size
For iCount = 1 To Max

c.Offset(0, 0).Value = arryMonthList(iCount)

'set c to the next cell down
Set c = c.Offset(1, 0)
Next iCount

End Sub

 

[Rick Rothstein \(MVP - VB\)]

First off, the Array function can only return its results to a Variant, not
a String array (the Split function, on the other hand, can return to both
data types). However, you do not need to do this as VBA has a built-in
MonthName function that you can use. Here is your loop with no arrays
needed...

For iCount = 1 To Max
c.Offset(0, 0).Value = MonthName(iCount)
Next

Rick

 

[RB Smissaert]

You need to do:

Dim arryMonthList

So, declared as a Variant.

Then after doing arryMonthList = Array("January", etc.
You will have an 0-based variant array holding the months in elements 0 to
11.


RBS

 

[TomPl]

Try something like this:

Option Explicit

Sub InsertMonths()

Dim aryMonths() As Variant
Dim lngRow As Long
Dim varMonth As Variant

aryMonths = Array("Jan", "Feb", "Mar", _
"Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
lngRow = 1
For Each varMonth In aryMonths
lngRow = lngRow + 1
ActiveSheet.Cells(lngRow, 2).Value = varMonth
Next

End Sub

 

[Scott]

Thanks, I learned something from your example.

Try something like this:

Option Explicit

Sub InsertMonths()

Dim aryMonths() As Variant
Dim lngRow As Long
Dim varMonth As Variant

aryMonths = Array("Jan", "Feb", "Mar", _
"Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
lngRow = 1
For Each varMonth In aryMonths
lngRow = lngRow + 1
ActiveSheet.Cells(lngRow, 2).Value = varMonth
Next

End Sub

 

[Dana DeLouis]

...to populate the range (B1:B12) with the 12 month names.

Just to be different...

With Application
[B1:B12] = .Transpose(.GetCustomListContents(4))
End With