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

Question

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

to look for specific headers (if they exist in the file), then
selects the entire column and
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.

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

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

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

Slai · Accepted Answer · 2017-04-28 18:57:59Z

3

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

answered Apr 28, 2017 at 18:57

Slai

23k5 gold badges49 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Shai Rado · Accepted Answer · 2017-04-28 18:37:48Z

3

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

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

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

edited Apr 28, 2017 at 18:37

answered Apr 28, 2017 at 18:34

Shai Rado

33.7k7 gold badges32 silver badges56 bronze badges

1 Comment

Seth Over a year ago

this is so cool! worked like a charm! thank you so much for your prompt answer!

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related