Excel/VBA:跳过错误并继续执行代码 [英] Excel/VBA: Skip error and continue w/ code execution

问题描述

问题:我的宏无法执行我想要的操作.我有一个包含多列的Excel文件.我想要的是宏

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

1. 查找特定的标题(如果文件中存在)，然后
2. 选择整个列，然后
3. 按照脚本中的指定调整大小.如果文件中不存在指定的标头，则代码应继续前进，而不会出现任何错误.

下面的代码将问题描述"的大小从50更改为6，尽管需要采取纠正措施?"的大小为6.标头(在这种情况下不适用，因为该标头不存在，因此大小调整要求为6 s/b会被忽略).

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.

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

我应该使用其他方法来编写此宏，并避免使用OnErrorResumeNext吗?

Should I use a different method to write this macro and avoid using 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

推荐答案

错误恢复下一个恢复到下一个行"，但3行可以合并为1:

On Error Resume Next resumes to the next "line", but the 3 lines can be combined into 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

这篇关于Excel/VBA:跳过错误并继续执行代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！