CODEWITHSUNDEEP
excelvbaerror-handlingruntime-error
MMMM
MMMM

Reputation: 29

VBA Run-Time Error 9 When Sheet Name Does Not Match the Target Sheet Name

I have the following code which converts Excel Sheets with a specific sheet names into PDF and then it loops in the folder. The User can input the sheet name into the tool for the code to loop for. However, if the sheet name is incorrect or does not exist, it shows Run-Time Error 9: Subscript out of Range. Instead of this error, I would like to get a MsgBox then Exit the Sub. I tried using On Error GoTo approach, which works when the code doesnt match the sheet name to the reference cell and shows the appropriate message. However, when the correct sheet name is inserted, it shows that message and does not proceed with the code as well.

How can I fix this so I'll only get the message when the code does not find the sheet name, and in case it does, it completes the code?

This is where I'm facing the issue 

On Error GoTo ErrorExit

'Even when the cell value matches the sheet's name, I still get an error and it exist the sub
Set reportSheet = Sheets(reportSheetName)   

ErrorExit:
MsgBox "Incorrect Sheet Name or It Does Not Exist"
Exit Sub

Dim settingsSheet As Worksheet       'Source
Dim reportSheet As Worksheet        'To convert to PDF
Dim targetColumnsRange As Range     'feeds from source
Dim targetRowsRange As Range
Dim reportSheetName As String       'source sheet with the target's sheet name
Dim reportColumnsAddr As String
Dim reportRowsAddr As String
    ' Set a reference to the settings sheet

Set settingsSheet = ThisWorkbook.Worksheets("Sheet1")   ' source

    ' Gather the report sheet's name

reportSheetName = settingsSheet.Range("C7").Value       ' good

On Error GoTo ErrorExit

'If this doesnt match, display the message and exit sub, else continue the sub
Set reportSheet = Sheets(reportSheetName)   

ErrorExit:
MsgBox "Incorrect Sheet Name or It Does Not Exist"
Exit Sub

Upvotes: 0

Views: 1059

Answers (1)

Tim Williams
Tim Williams

Reputation: 166825

You can do it like this:

On Error Resume Next  'ignore errors
Set reportSheet = Sheets(reportSheetName) 
On Error Goto 0       'stop ignoring errors 

If reportSheet is nothing then
     Msgbox "no sheet named '" & reportSheetName & "' in this workbook!"
Else
     'all good
End If

Upvotes: 2

Related Questions