Reputation: 15
Problem: My macro doesn't do what I want. I have an excel file with multiple columns. What I want is the macro
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
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
Reputation: 33692
Here is an example of one of your Find
s, 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