Array Variable for Month Names

S

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
 
R

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
 
R

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
 
T

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
 
S

Scott

Thanks, I learned something from your example.


TomPl said:
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
 
D

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top