CODEWITHSUNDEEP
vbaexcel
Seth
Seth

Reputation: 15

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

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

  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.

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

Upvotes: 1

Views: 44580

Answers (2)

Slai
Slai

Reputation: 22896

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

Upvotes: 3

Shai Rado
Shai Rado

Reputation: 33692

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

Upvotes: 3

Related Questions