CODEWITHSUNDEEP
excelvba
Ajay
Ajay

Reputation: 77

Get last 6 months name in Array

Want to get list of last 6 months name in array mon but get error as Subscript out of range at line

mon(X) = val

Any idea where i am wrong,output shoule be like

mon = ("DecWK1","DecWK2","DecWK3","DecWK4","Dec","JanWK1","JanWK2","JanWK3","JanWK4","Jan",.......)

Main Code

Sub ColorRows()

Dim mon() As Variant, MonthName() As Variant, X As Long
Dim val As String

X = 0
ReDim Preserve mon(X)

MonthName = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")

Dim monName As String

For i = -6 To 0
  '  MsgBox i
    monName = Format(DateAdd("M", i, Now), "MMMM")
   ' MsgBox sMonth_Name


   For Each element In MonthName
   ReDim Preserve mon(0 To X)
        If InStr(monName, element) Then
            For j = 1 To 4
                val = element & "WK" & j
                mon(X) = val
                X = X + 1
            Next j

            mon(X) = element
            X = X + 1
        End If

    Next element
Next i

End Sub

Upvotes: 1

Views: 122

Answers (1)

JMP
JMP

Reputation: 4467

When you ReDim your array, you aren't taking into account the new elements to be added.

Write:

 ReDim Preserve mon(0 To X + 4)

instead.

Upvotes: 1

Related Questions