vba_schedule

Sub SchePHI()

'fuzhi xuyao biaoge
Dim MyBook1 As Workbook
Set MyBook1 = ActiveWorkbook

With Application.FileDialog(msoFileDialogFilePicker)
    .Title = "Select A File"
    .InitialFileName = "\\cngscapl-spf303\Customer_Care\Doc_Import\Share\AM Team\01 IB - LARA\15 Personal Folder\bOB"
    .AllowMultiSelect = False
    .Filters.Clear
    .Filters.Add "Text File", "*.txt"
    .Filters.Add "EXCEL File", "*.xlsx; *.xls", 1
    .Filters.Add "All File", "*.*", 1
    If .Show Then
        .ButtonName = "Select Me"
        Set ipath = .SelectedItems
    End If
End With

If IsEmpty(ipath) Then Exit Sub
ipath = ipath(1)

Dim MyBook2, MyBook3, MyBook4 As Workbook
Set MyBook2 = Workbooks.Open(ipath)
MyBook2.Sheets("Page1_1").Copy MyBook1.Sheets("page")
MyBook2.Close
MyBook1.Sheets("Page1_1").Name = "Schedule Report"

Workbooks.Open ("\\cngscapl-spf303\Customer_Care\Doc_Import\Share\AM Team\01 IB - LARA\15 Personal Folder\bOB\PHI Schedule Atuo\PHI Schedule History.xlsx")
Set MyBook3 = ActiveWorkbook
MyBook3.Sheets(1).Copy MyBook1.Sheets("page")
MyBook3.Close
MyBook1.Sheets("sheet1").Name = "Schedule History"

Workbooks.Open ("\\cngscapl-spf303\Customer_Care\Doc_Import\Share\AM Team\01 IB - LARA\15 Personal Folder\bOB\PHI Schedule Atuo\PHI PIC.xlsx")
Set MyBook4 = ActiveWorkbook
MyBook4.Sheets(1).Copy MyBook1.Sheets("page")
MyBook4.Close
MyBook1.Sheets("sheet1").Name = "PIC"

' quchong
Sheets.Add
Sheets("sheet4").Name = "working"
Sheets("page").Range("A:A,C:C,I:I,L:L").Copy Sheets("working").[a1]
Sheets("working").Cells.RemoveDuplicates Columns:=Array(1, 2, 3, 4) _
        , Header:=xlYes
        

'tiqu chuangtizhi


Dim EJ As Date


EJ = ScheuD.TextBox2.Value

'SHUJUZHENGLI

For X1 = 1 To Sheets("Schedule History").Range("a65536").End(xlUp).ROW + 50

Sheets("Schedule History").Cells(X1, "m").Value = Sheets("Schedule History").Cells(X1, "B").Value & Sheets("Schedule History").Cells(X1, "C").Value

Next X1


For X3 = 1 To Sheets("PIC").Range("a65536").End(xlUp).ROW

Sheets("PIC").Cells(X3, "D") = Sheets("PIC").Cells(X3, "B") & "/" & Sheets("PIC").Cells(X3, "C")

Next X3


For X5 = 1 To Sheets("Schedule Report").Range("a65536").End(xlUp).ROW

Sheets("Schedule Report").Cells(X5, "X") = Sheets("Schedule Report").Cells(X5, "J") & Sheets("Schedule Report").Cells(X5, "H")

Next X5


'sainchu PA,ETA,ZUOGUODECHUAN

Dim ROW As Integer

Dim SCH()

ROW = Sheets("working").Range("a65536").End(xlUp).ROW

ReDim SCH(1 To ROW, 1 To 13)

For X2 = 2 To ROW

If "PH" = Left(Sheets("working").Cells(X2, "d"), 2) _
And Sheets("working").Cells(X2, "c") <= EJ _
And IsError(Application.VLookup(Sheets("working").Cells(X2, "b") & Sheets("working").Cells(X2, "d"), Sheets("Schedule History").Range("M:M"), 1, False)) Then

SCH(X2, 1) = Left(Sheets("working").Cells(X2, "a"), 3)
SCH(X2, 2) = Sheets("working").Cells(X2, "b")
SCH(X2, 3) = Sheets("working").Cells(X2, "c")
SCH(X2, 4) = Sheets("working").Cells(X2, "d")
SCH(X2, 10) = Sheets("working").Cells(X2, "b") & Sheets("working").Cells(X2, "d")

'chaxun schedule LAST FOREIGHT PORT & EXPORT VOYAGE

On Error Resume Next
'LAST port etd
SCH(X2, 6) = Sheets("Schedule Report").Range("X:X").Find(SCH(X2, 10)).Offset(0, -9)
SCH(X2, 7) = Sheets("Schedule Report").Range("X:X").Find(SCH(X2, 10)).Offset(0, -7)

SCH(X2, 11) = Sheets("Schedule Report").Range("X:X").Find(SCH(X2, 10)).Offset(0, -5)
SCH(X2, 12) = Sheets("Schedule Report").Range("X:X").Find(SCH(X2, 10)).Offset(0, -3)
On Error GoTo 0

'chaxun pic

SCH(X2, 8) = Application.VLookup(SCH(X2, 1), Sheets("PIC").Range("A:D"), 4, False)

End If

Next X2


'shuchu



   Sheets.Add
   ActiveSheet.Name = "Schedulewk"

Sheets("Schedulewk").[a1] = "Regin"
Sheets("Schedulewk").[a2] = "PHI"
Sheets("Schedulewk").[b1] = "GSC PIC"
Sheets("Schedulewk").[c1] = "Service"
Sheets("Schedulewk").[d1] = "Import Voyage"
Sheets("Schedulewk").[e1] = "POD"
Sheets("Schedulewk").[F1] = "ETD"
Sheets("Schedulewk").[G1] = "ETA"
Sheets("Schedulewk").[H1] = "Conmments"

'qukong

Dim SCH1()

ReDim SCH1(1 To ROW, 1 To 9)

For X2 = 2 To ROW

If SCH(X2, 1) <> "" Then

SCH1((k + 1), 1) = SCH(X2, 1)
SCH1((k + 1), 2) = SCH(X2, 2)
SCH1((k + 1), 3) = SCH(X2, 3)
SCH1((k + 1), 4) = SCH(X2, 4)
SCH1((k + 1), 5) = SCH(X2, 5)
SCH1((k + 1), 8) = SCH(X2, 8)


If Left(SCH(X2, 6), 2) <> "PH" Then

SCH1((k + 1), 7) = SCH(X2, 7)

ElseIf Left(SCH(X2, 6), 2) = "PH" And Left(SCH(X2, 11), 2) <> "PH" Then

SCH1((k + 1), 7) = SCH(X2, 12)

ElseIf Left(SCH(X2, 6), 2) = "PH" And Left(SCH(X2, 11), 2) = "PH" Then

SCH1((k + 1), 7) = "not find"

End If


k = k + 1
        
    End If
    
Next X2


For X4 = 1 To ROW


Sheets("Schedulewk").Cells(X4 + 1, "b") = SCH1(X4, 8)
Sheets("Schedulewk").Cells(X4 + 1, "c") = Left(SCH1(X4, 1), 3)
Sheets("Schedulewk").Cells(X4 + 1, "D") = SCH1(X4, 2)
Sheets("Schedulewk").Cells(X4 + 1, "E") = SCH