On Error Help

[drozek]

How would I do the following?

If I get an error with the following code:

Cells.Find(What:="Ship - To", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate

It would run the Sub Named OneShipTo

 

[DanteAmor]

How about

Sub test()
  Dim f As Range
  Set f = Cells.Find("Ship - To", , xlValues, xlPart, , xlNext, False, , False)
  If f Is Nothing Then
    MsgBox "Not found"
  Else
    f.Activate
  End If
End Sub

 

[drozek]

How about

Sub test()
  Dim f As Range
  Set f = Cells.Find("Ship - To", , xlValues, xlPart, , xlNext, False, , False)
  If f Is Nothing Then
    MsgBox "Not found"
  Else
    f.Activate
  End If
End Sub

That would work, but if there isn't a "Ship - To" cell there is there a sub that looks for "Material" and does additional filtering. Thats why I need to call OneShipTo() if it doesn't find "Ship - To"

 

[DanteAmor]

Try this

Sub test()
  Dim f As Range
  Set f = Cells.Find("Ship - To", , xlValues, xlPart, , xlNext, False, , False)
  If f Is Nothing Then
    call OneShipTo 
  Else
    f.Activate
  End If
End Sub

 

[drozek]

Here is the code I have:

It should look for "Ship - To" and if it doesn't find "Ship - To" it could go to the sub OneShipTo

I am currently getting an error with this code.

Sub ColorEnds()
'
' ColorEnds Macro
' Ctrl+Shift+E
'
    Dim ShipColumn As Integer
    Dim FirstColumn As Integer
    Dim Material As Integer
    Dim f As Range
    
    Set f = Cells.Find("Ship - To", , xlValues, x1Part, , xlNext, False, , False)
    If f Is Nothing Then
        Call OneShipTo
        
    Else
        f.Activate
    End If
    End Sub
Sub FindMonth()

    Cells.Find(What:="M 0*", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
        
    ActiveCell.Offset(1, 0).Select
    ActiveWindow.FreezePanes = True

End Sub
Sub MultipleShipTo()

    Cells.Find(What:="Ship - To", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    ShipColumn = ActiveCell.Column
    
     Cells.Find(What:="DP FORECAST", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    FirstColumn = ActiveCell.Column
    
    Rows("6:6").Select
    Selection.AutoFilter
    
    With ActiveSheet.Range("A5:G" & Range("A" & Rows.Count).End(3).Row)
       .AutoFilter FirstColumn, "Sales Input"
       .AutoFilter ShipColumn, "<>Total"
    End With
    
    Columns("A:A").ColumnWidth = 0
    Rows("1:5").Select
    Range("A5").Activate
    Selection.EntireRow.Hidden = True
    
    Cells.Select
    Cells.EntireColumn.AutoFit
    
    Call FindMonth



End Sub
Sub OneShipTo()

    Cells.Find(What:="Material", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    Material = ActiveCell.Column
    
         Cells.Find(What:="DP FORECAST", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    FirstColumn = ActiveCell.Column
    
    Rows("6:6").Select
    Selection.AutoFilter
    
    With ActiveSheet.Range("A5:G" & Range("A" & Rows.Count).End(3).Row)
       .AutoFilter FirstColumn, "Sales Input"
       .AutoFilter Material, "<>Total"
    End With
    
    Columns("A:A").ColumnWidth = 0
    Rows("1:5").Select
    Range("A5").Activate
    Selection.EntireRow.Hidden = True
    
    Cells.Select
    Cells.EntireColumn.AutoFit
    
    Call FindMonth

End Sub

 

[drozek]

I think I got it figured

Try this

Sub test()
  Dim f As Range
  Set f = Cells.Find("Ship - To", , xlValues, xlPart, , xlNext, False, , False)
  If f Is Nothing Then
    call OneShipTo
  Else
    f.Activate
  End If
End Sub

Thanks your code helped, one last question about sorting

How would I sort ShipColumn from small to largest?

ShipColumn.Sort(Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal)

Sub MultipleShipTo()

    Cells.Find(What:="Ship - To", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    ShipColumn = ActiveCell.Column
    
     Cells.Find(What:="DP FORECAST", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    FirstColumn = ActiveCell.Column
    
    Rows("6:6").Select
    Selection.AutoFilter
    
    With ActiveSheet.Range("A5:G" & Range("A" & Rows.Count).End(3).Row)
       .AutoFilter FirstColumn, "Sales Input"
       .AutoFilter ShipColumn, "<>Total"
    End With
    
    ShipColumn.Sort(Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal)
        
    Columns("A:A").ColumnWidth = 0
    Rows("1:5").Select
    Range("A5").Activate
    Selection.EntireRow.Hidden = True
    
    Cells.Select
    Cells.EntireColumn.AutoFit
    
    Call FindMonth



End Sub

 

[DanteAmor]

I am currently getting an error with this code.

You must provide more information: What error message? and which line does the macro stop?

Set f = Cells.Find("Ship - To", , xlValues, x1Part, , xlNext, False, , False)

You put a number one ( 1 ) and it must be the letter ( l )

Set f = Cells.Find("Ship - To", , xlValues, xlPart, , xlNext, False, , False)

 

[drozek]

You must provide more information: What error message? and which line does the macro stop?

Set f = Cells.Find("Ship - To", , xlValues, x1Part, , xlNext, False, , False)

You put a number one ( 1 ) and it must be the letter ( l )

Set f = Cells.Find("Ship - To", , xlValues, xlPart, , xlNext, False, , False)

Yeah I typed it wrong, too much whiskey. I copied it and pasted. I asked a sorting question above your response.

 

[DanteAmor]

How would I sort ShipColumn from small to largest?

What do you want to sort?
A whole range based on the column found?
Or just the data in one column?

 

[drozek]

What do you want to order?
A whole range based on the column found?
Or just the data in one column?

I want to order ShipColumn, from smallest to largest