Excel/VBA：跳过错误并继续执行代码

Excel/VBA: Skip error and continue w/ code execution

提问by Seth

Problem: My macro doesn't do what I want. I have an excel file with multiple columns. What I want is the macro

问题：我的宏没有做我想要的。我有一个多列的excel文件。我想要的是宏

1. to look for specific headers (if they exist in the file), then
2. selects the entire column and
3. resize it as specified in the script. If the specified header doesn't exist in the file, the code should move on the next one without giving any error.

1. 寻找特定的头文件（如果它们存在于文件中），然后
2. 选择整列并
3. 按照脚本中的指定调整其大小。如果文件中不存在指定的标头，则代码应移至下一个标头而不会出现任何错误。

The code below changes the "Problem Description" size from 50 to 6 although 6 is the size for "Corrective Action Required?" header (which is not applicable in this case as that header doesn't exist and hence the resizing requirement of 6 s/b simply ignored).

下面的代码将“问题描述”的大小从 50 更改为 6，尽管 6 是“需要纠正措施”的大小？标头（在这种情况下不适用，因为该标头不存在，因此 6 s/b 的调整大小要求被简单地忽略）。

But that didn't happened. Instead, the size of previous condition (changing the column size of "Problem Description" to 50 ) did change to 6.

但这并没有发生。相反，先前条件的大小（将“问题描述”的列大小更改为 50 ）确实更改为 6。

Should I use a different method to write this macro and avoid using OnErrorResumeNext?

我应该使用不同的方法来编写这个宏并避免使用 OnErrorResumeNext 吗？

Sub Resize_specific_columns_OnErrResNxt()

'
' finds specific columns based on changed header names and resize them


    On Error Resume Next
     Cells.Find(what:="data domain", After:=ActiveCell, LookIn:= _
        xlValues, lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext _
        , MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.EntireColumn.Select
    Selection.ColumnWidth = 8


    On Error Resume Next
     Cells.Find(what:="eDIM#", After:=ActiveCell, LookIn:= _
        xlValues, lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext _
        , MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.EntireColumn.Select
    Selection.ColumnWidth = 6


    On Error Resume Next
     Cells.Find(what:="Problem Description", After:=ActiveCell, LookIn:= _
        xlValues, lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext _
        , MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.EntireColumn.Select
    Selection.ColumnWidth = 50


    On Error Resume Next
     Cells.Find(what:="Corrective Action Required?", After:=ActiveCell, LookIn:= _
        xlValues, lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext _
        , MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.EntireColumn.Select
    Selection.ColumnWidth = 6

回答by Shai Rado

Here is an example of one of your Finds, you can copy>>paste this method to the other ones.

这是您Find的一个示例，您可以将此方法复制>>粘贴到其他方法。

The recommended way to use a Findis to Set a Range to the Findresult, and afterwards you can check if the Range = Is Nothing, which means the Findwas unsuccessful finding the text/number you were looking for.

使用 a 的推荐方法Find是为结果设置一个范围Find，然后您可以检查Range = Is Nothing，这意味着Find未成功找到您要查找的文本/数字。

Code

代码

' finds specific columns based on changed header names and resize them
Dim FndRng As Range

Set FndRng = Cells.Find(what:="data domain", After:=ActiveCell, LookIn:= _
    xlValues, lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext _
    , MatchCase:=False, SearchFormat:=False)

If Not FndRng Is Nothing Then '<-- find was successful
    FndRng.EntireColumn.ColumnWidth = 8
End If
Set FndRng = Nothing '<-- clear Range before next Find

回答by Slai

On Error Resume Nextresumes to the next "line", but the 3 lines can be combined into 1:

On Error Resume Next继续到下一个“行”，但 3 行可以合并为 1：

On Error Resume Next

Cells.Find("data domain").EntireColumn.ColumnWidth = 8

Cells.Find("eDIM#").EntireColumn.ColumnWidth = 6

Cells.Find("Problem Description").EntireColumn.ColumnWidth = 50

Cells.Find("Corrective Action Required?").EntireColumn.ColumnWidth = 6

On Error Goto 0     ' optional if there is more code after that should not ignore errors