工作表名称与目标工作表名称不匹配时的VBA运行时错误9 [英] VBA Run-Time Error 9 When Sheet Name Does Not Match the Target Sheet Name

问题描述

我有以下代码，该代码将具有特定工作表名称的Excel工作表转换为PDF，然后在文件夹中循环。用户可以将工作表名称输入到工具中以循环查找代码。但是，如果工作表名称不正确或不存在，则会显示运行时错误9：下标超出范围。代替此错误，我想获取一个MsgBox，然后退出Sub。我尝试使用On Error GoTo方法，该方法在代码与工作表名称与参考单元格不匹配并显示适当的消息时有效。但是，当插入正确的工作表名称时，它会显示该消息，并且不会继续执行代码。

如何解决此问题，以便仅在代码未找到工作表名称的情况下得到消息，如果能找到，它会完成代码？ / p>

这是我面临的问题

 出错时转到ErrorExit 
 
'即使单元格值与工作表名称匹配，我仍然会收到一个错误，并且它存在子
 Set reportSheet = Sheets（reportSheetName）
 
 ErrorExit ：
 MsgBox不正确的工作表名称或不存在 
退出子
  

 调暗设置作为工作表'来源
调暗报表作为工作表'要转换为PDF 
调暗targetColumnsRange作为范围'来自源
的提要Dim targetRowsRange作为范围
 Dim reportSheetName As String'具有目标工作表名称的源工作表
 Dim reportColumnsAddr As String 
 Dim reportRowsAddr As String 
'设置对设置工作表
 $ b $的引用b设置settingsSheet = ThisWorkbook.Wor ksheets（ Sheet1）'源
 
'收集报告表的名称
 
 reportSheetName = settingsSheet.Range（ C7）。Value'好
 
出现错误时GoTo ErrorExit 
 
'如果不匹配，则显示消息并退出子菜单，否则继续子菜单
设置reportSheet = Sheets（reportSheetName）
 
 ErrorExit：
 MsgBox不正确的工作表名称或不存在 
退出子
  

解决方案

您可以这样操作：

  On Error Resume Next'忽略错误
设置reportSheet = Sheets（reportSheetName）
出错时转到0'停止忽略错误
 
如果reportSheet为空，则
 Msgbox没有名为的表& reportSheetName& ’在本工作簿中！ 
其他
都很好
如果
结束$  

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

解决方案

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

这篇关于工作表名称与目标工作表名称不匹配时的VBA运行时错误9的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！