CODEWITHSUNDEEP
vbaexcelruntime-error
user3094302
user3094302

Reputation: 15

Runtime error "9" in VBA[excel] - Subscript out of range

I am currently working on a code that takes the date from the user, opens a calendar, sees if the month is present, and if it isn't, creates a worksheet with that month name.

The code looks like this where m_y is a string, and has values such as "January 2014" [the " signs included]:

Sub addmonth(m_y)
  On Error Resume Next
  CalendarWorkbook.Worksheets(m_y).Select

  If Err.Number<>0 Then
     'code to add sheet and format it

I tried putting it in a With/End With command, I have no Option Explicit in the code. Other methods mentioned in answers such as using the .Range() instead of the .Select; however I had no luck in succeeding.

Any help provided would be appreciated.

Upvotes: 0

Views: 3612

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149295

.Select in most cases is the main cause of runtime errors. I believe you have another workbook open. INTERESTING READ

Try this another way which doesn't use .Select

Option Explicit

Sub test()
    addmonth ("""Feb2015""")
End Sub

Sub addmonth(m_y)
    Dim calendarworkbook As Workbook
    Dim ws As Worksheet

    Set calendarworkbook = ThisWorkbook

    On Error Resume Next
    Set ws = calendarworkbook.Worksheets(m_y)
    On Error GoTo 0

    If ws Is Nothing Then calendarworkbook.Worksheets.Add.Name = m_y
End Sub

Note: OERN (On Error Resume Next) should be used judiciously. Ensure that it just curbs the message for only the part that you want and not for the rest. Else it is not good error handling :)

Upvotes: 1

sam092
sam092

Reputation: 1335

This worked for me

Sub test()
    addmonth ("""Feb2015""")
End Sub

Sub addmonth(m_y)
    Dim calendarworkbook As Workbook
    Set calendarworkbook = ThisWorkbook
    On Error Resume Next
    calendarworkbook.Worksheets(m_y).Select

    If Err.Number <> 0 Then
        With calendarworkbook.Worksheets.Add
            .Name = m_y
        End With
    End If
End Sub

Upvotes: 0

Related Questions