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.
.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 :)
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