Excel VBA Beginner - Getting Run time error '9' Subscript out of range

Question

I am trying to write a simple macro to change a date format mm/dd/yy to just the month full name. This is what I have using a days worth experience in VBA. I'm missing the error here

Sub ChangeDate()

    Dim Last As Integer
    Last = Cells(Rows.Count, 1).End(xlUp).Row
    Dim Counter As Integer

    For Counter = 2 To Last
        Dim Month As String
        Month = Left(Worksheets("Sheet1").Cells(Counter, 2), 1)

        If Month = "7" Then
            Worksheets("Sheet1").Cells(Counter, 2).Value = "July"

        ElseIf Month = "8" Then
            Worksheets("Sheet1").Cells(Counter, 2).Value = "August"

        ElseIf Month = "9" Then
            Worksheets("Sheet1").Cells(Counter, 2).Value = "September"
        End If

    Next Counter

End Sub

Worksheets("Sheet1").Cells(Counter, 2).Value = Format(Worksheets("Sheet1").Cells(Counter, 2).Value2,"mmmm") — Scott Craner
... or just change Worksheets("Sheet1").range("B2:B" & last).numberformat = "mmmm" — user4039065
Why you need to use code? You can do it changing Format Cells to Custom using mmm — user8753746
For future reference, a multiple If ... ElseIf ... end if like this is a good place for a Select Case statement. — user4039065

Unknown Unknown · Accepted Answer · 2017-10-24T19:49:40

Try using this code:

Sub ChangeDate()
    Dim Counter As Long
    Dim Last As Integer

    Worksheets("Sheet1").Select
    Last = Cells(Rows.Count, 1).End(xlUp).Row

    For Counter = 2 To Last
        Dim Month As String
        Month = Left(Worksheets("Sheet1").Cells(Counter, 1), 1)

        If Month = "7" Then
        Worksheets("Sheet1").Cells(Counter, 2).Value = "July"

        ElseIf Month = "8" Then
        Worksheets("Sheet1").Cells(Counter, 2).Value = "August"

        ElseIf Month = "9" Then
        Worksheets("Sheet1").Cells(Counter, 2).Value = "September"
        End If
    Next Counter
End Sub

This code reads the date in column 1 and puts the month in column 2. Is a good advice to use Case statement instead of ElseIf.

Excel VBA Beginner - Getting Run time error '9' Subscript out of range

1 Answers