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

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


"Scott" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
>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

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


"Scott" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
>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

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" wrote:

> 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
>
>

Thanks, I learned something from your example.


"TomPl" <(E-Mail Removed)> wrote in message
news:0EDFA516-CE26-466F-8C3F-(E-Mail Removed)...
> 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" wrote:
>
>> 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
>>
>>

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

Just to be different...

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

--
HTH :>)
Dana DeLouis


"Scott" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
>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