On Error GoTo (Name)

[kmmsquared]

Hi,

I am attempting to run the following code: If I don't have the variables present in the AutoFilter, Excel gives me an error and doesn't go to the next step--it only works the first time. Any ideas on why this is and how to fix it?

Sub Latest_Scheduled_Delivery_Date()
'Copy/Paste Cell Values
Columns("T:V").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Dim lngLastRow As Long
With Sheets("BOM")
      lngLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
'Insert Row
 Columns("W:W").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
'Insert Formula into new Row
Range("W2:W" & lngLastRow).Formula = "=IF(AND(T2<>"""",U2<>"""",V2<>"""",EXACT(T2,U2),EXACT(U2,V2)),1,IF(AND(T2<>"""",U2<>"""",V2<>"""",T2<>U2,U2=V2),2,IF(AND(T2<>"""",U2<>"""",V2<>"""",T2<>U2,U2<>V2),3,IF(AND(T2<>"""",U2<>"""",V2<>"""",EXACT(T2,U2),U2<>V2),4,IF(AND(T2<>"""",U2<>"""",V2="""",T2<>U2),5,IF(AND(T2<>"""",U2<>"""",V2="""",EXACT(T2,U2)),6,IF(AND(T2="""",U2="""",V2<>""""),7,IF(AND(T2="""",U2="""",V2=""""),8,""""))))))))"
Columns("W").Select
Selection.NumberFormat = "General"
Columns("W").Copy
Columns("W").Select
Selection.PasteSpecial Paste:=xlPasteValues
 
'Autofilter
Columns("W").Select
Selection.AutoFilter Field:=1, Criteria1:="1", Operator:=xlOr, Criteria2:="2"
[COLOR=red]On Error GoTo Step2:[/COLOR]
Range("V2:V" & lngLastRow).SpecialCells(xlCellTypeVisible).Select
Selection.ClearContents
 
Step2:
Worksheets("BOM").AutoFilterMode = False
Columns("W").Select
Selection.AutoFilter Field:=1, Criteria1:="3", Operator:=xlOr, Criteria2:="4"
[COLOR=red]On Error GoTo Step3:[/COLOR]
Range("V2:V" & lngLastRow).SpecialCells(xlCellTypeVisible).Select
Selection.Interior.ColorIndex = 22
 
Step3:
Worksheets("BOM").AutoFilterMode = False
Columns("W").Select
Selection.AutoFilter Field:=1, Criteria1:="4", Operator:=xlOr, Criteria2:="6"
[COLOR=red]On Error GoTo Step4:[/COLOR]
Range("T2:T" & lngLastRow).SpecialCells(xlCellTypeVisible).Select
Selection.ClearContents
 
Step4:
Worksheets("BOM").AutoFilterMode = False
Columns("W").Select
Selection.AutoFilter Field:=1, Criteria1:="7"
[COLOR=red]On Error GoTo Step5:[/COLOR]
Range("U2:U" & lngLastRow).SpecialCells(xlCellTypeVisible).Select
Selection.Formula = "=RC[1]"
Worksheets("BOM").AutoFilterMode = False
Columns("U").Copy
Columns("U").Select
Selection.PasteSpecial Paste:=xlPasteValues
Columns("W").Select
Selection.AutoFilter Field:=1, Criteria1:="7"
Range("V2:V" & lngLastRow).SpecialCells(xlCellTypeVisible).ClearContents
Worksheets("BOM").AutoFilterMode = False
Columns("W").Delete
 
Step5:
End With
End Sub

Any help would be greatly appreciated.

Thanks!

 

[Scott Huish]

Best to avoid the GoTo structure.

I usually use On Error Resume Next at the beginning of my code and then trap the expected error where it should occur.

Secondly, you rarely need to select anything in VBA. You can work with ranges directly. So for each section where you are using SpecialCells with a dimmed range variable (in this example v), do it like this:

Set v=Range("V2:V" & lngLastRow).SpecialCells(xlCellTypeVisible)
If Not v Is Nothing then v.ClearContents

 

[kmmsquared]

Since I need to AutoFilter for those different options, how should i isolate the error? The code works fine if all of the variables I am autofiltering for are present. I couldn't think of a way other than autofiltering to get the macro to do what i wanted it to, open to any suggestions! My macro skills are intermediate, and any insight would be helpful

 

[Scott Huish]

You put On Error Resume Next at the beginning of your code.
My second line of code is trapping the error. It will only execute the Then part if a Range was assigned to the variable.

 

[Marcelo Branco]

Maybe using Advanced Filter

Something like that (as a starter)

Adjust the range W2:W12 to Range("W2:W"&lngLastRow)

Sub Macro2()
'
' Macro2 Macro
'
'
    Range("Y2").Select
    ActiveCell.Formula = "=OR(W2=2,W2=3,W2=4,W2=5,W2=7)"
    Range("W1:W12").Select
    Range("W1:W12").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
        Range("Y1:Y2"), Unique:=False
End Sub

M.

 

[kmmsquared]

That could work but I need to isolate each variable 1,2,3,4,5,7 separately if they are present.

 

[kmmsquared]

@HOTPEPPER, that worked brilliantly, thank you!

 

[Marcelo Branco]

For 1 or 2 something like

Sub Macro2()
'
' Macro2 Macro
'
'
    Dim aCell As Range
    Range("Y2").Select
    ActiveCell.Formula = "=OR(W2=2,W2=3,W2=4,W2=5,W2=7)"
    Range("W1:W12").Select
    Range("W1:W12").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
        Range("Y1:Y2"), Unique:=False
    Range("W2:W12").SpecialCells(xlCellTypeVisible).Select
    For Each aCell In Selection
        If aCell.Value = 1 Or aCell.Value = 2 Then
            'Clear contents column V
            aCell.Offset(0, -1).ClearContents
        End If
        
        'and so on for 3, 4, 5, 7 etc
    Next aCell
End Sub

M.

 

[kmmsquared]

Thanks!