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]

Try this

Sub MultipleShipTo()
  Dim f As Range, ShipColumn As Long, lr As Long, FirstColumn As Long
  Set f = Cells.Find("Ship - To", , xlValues, xlPart, , xlNext, False, , False)
  If f Is Nothing Then
    MsgBox "Ship - To, Not found"
  Else
    ShipColumn = f.Column
    lr = Cells(Rows.Count, ShipColumn).End(3).Row
      
    Set f = Cells.Find(What:="DP FORECAST", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
    If f Is Nothing Then
      MsgBox "DP FORECAST, Not found"
    Else
      FirstColumn = f.Column
      
      Rows("6:6").AutoFilter
      With ActiveSheet.Range("A5:G" & Range("A" & Rows.Count).End(3).Row)
        .AutoFilter FirstColumn, "Sales Input"
        .AutoFilter ShipColumn, "<>Total"
      End With
      
      Range(Cells(6, ShipColumn), Cells(lr, ShipColumn)).Sort Cells(6, ShipColumn), 1, , , , , , xlYes
          
      Columns("A:A").ColumnWidth = 0
      Rows("1:5").Select
      Range("A5").Activate
      Selection.EntireRow.Hidden = True
      Cells.Select
      Cells.EntireColumn.AutoFit
      
      Call FindMonth
    End If
  End If
End Sub

 

[drozek]

Try this

Sub MultipleShipTo()
  Dim f As Range, ShipColumn As Long, lr As Long, FirstColumn As Long
  Set f = Cells.Find("Ship - To", , xlValues, xlPart, , xlNext, False, , False)
  If f Is Nothing Then
    MsgBox "Ship - To, Not found"
  Else
    ShipColumn = f.Column
    lr = Cells(Rows.Count, ShipColumn).End(3).Row
     
    Set f = Cells.Find(What:="DP FORECAST", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
    If f Is Nothing Then
      MsgBox "DP FORECAST, Not found"
    Else
      FirstColumn = f.Column
     
      Rows("6:6").AutoFilter
      With ActiveSheet.Range("A5:G" & Range("A" & Rows.Count).End(3).Row)
        .AutoFilter FirstColumn, "Sales Input"
        .AutoFilter ShipColumn, "<>Total"
      End With
     
      Range(Cells(6, ShipColumn), Cells(lr, ShipColumn)).Sort Cells(6, ShipColumn), 1, , , , , , xlYes
         
      Columns("A:A").ColumnWidth = 0
      Rows("1:5").Select
      Range("A5").Activate
      Selection.EntireRow.Hidden = True
      Cells.Select
      Cells.EntireColumn.AutoFit
     
      Call FindMonth
    End If
  End If
End Sub

You are a life saver! Love you!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[drozek]

I'm glad to help you. Thanks for the feedback.

Ok maybe one more question:

How would I would sort Material the same way as the one above?

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

 

[DanteAmor]

Try this

Sub OneShipTo()
  Dim f As Range, material As Long, FirstColumn As Long
  Set f = Cells.Find("Material", , xlValues, xlPart, , xlNext, False, , False)
  If f Is Nothing Then
    MsgBox "Material not found"
  Else
    material = f.Column
    Set f = Cells.Find("DP FORECAST", , xlValues, xlPart, , xlNext, False, , False)
    If f Is Nothing Then
      MsgBox "DP FORECAST not found"
    Else
      FirstColumn = f.Column
      Rows("6:6").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").EntireRow.Hidden = True
      Cells.EntireColumn.AutoFit
      Call FindMonth
    End If
  End If
End Sub

 

[drozek]

Try this

Sub OneShipTo()
  Dim f As Range, material As Long, FirstColumn As Long
  Set f = Cells.Find("Material", , xlValues, xlPart, , xlNext, False, , False)
  If f Is Nothing Then
    MsgBox "Material not found"
  Else
    material = f.Column
    Set f = Cells.Find("DP FORECAST", , xlValues, xlPart, , xlNext, False, , False)
    If f Is Nothing Then
      MsgBox "DP FORECAST not found"
    Else
      FirstColumn = f.Column
      Rows("6:6").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").EntireRow.Hidden = True
      Cells.EntireColumn.AutoFit
      Call FindMonth
    End If
  End If
End Sub

You da man!

 

[DanteAmor]

Again with pleasure. Thanks for the feedback

 

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

Hey Friend, so I thought this was working here is the issue, with pictures included. It sorts the ship to, but the data to the right doesn't properly sort.

      Range(Cells(6, ShipColumn), Cells(lr, ShipColumn)).Sort Cells(6, ShipColumn), 1, , , , , , xlYes

Ship 1 picture is the one before the sort.

Ship 2 is after that VBA code to sort ship to

Ship 3 is if i stop the macro right before the vba code and I manual sort from smallest to largest, you can see ship to 49145 moves to row 104 and 666 in column J is there.

Any ideas?

 

[DanteAmor]

I want to order ShipColumn, from smallest to largest

 

[drozek]

there was nothing in your response