Sub NewUpdateLoads()

Application.ScreenUpdating = False

''Assumptions''



Dim Loads, Data, Carriers, Invoices, Quotes As Worksheet
Dim StartRow_Q, Col_Q, StartRow_L, Col_L, N, i As Integer
Dim SearchRange, BolNumR As Range
Dim SearchVal As String
Dim DataRow, CarriersRow, InvoicesRow, shprow As Integer

Dim StatusLink As String
Dim StatusText As String
Dim ShipDate As Variant
Dim OfferedLane As String
Dim Customer As String
Dim Equipment As String
Dim Ratee As Variant
Dim Profit As Variant
Dim Carrier As String
Dim CarrierEmail As String
Dim CarrierPhone As String
Dim InvoiceStatus As String
Dim CustomerInvoiced As String
Dim InvoiceAge As Variant
Dim NewNumber As String

'To see how long the macro takes to run
Dim StartTime As Double
Dim SecondsElapsed As Double

StartTime = Timer

Set Quotes = Worksheets("Quotes")
Set Loads = Worksheets("Loads")
Set Data = Worksheets("Data")
Set Carriers = Worksheets("Carriers")
Set Invoices = Worksheets("Invoices")

'number of rows in each sheet
DataRow = Invoices.Cells(Rows.Count, 4).End(xlUp).Row + 1000
'MsgBox DataRow
CarriersRow = Carriers.Cells(Rows.Count, 4).End(xlUp).Row
InvoicesRow = Invoices.Cells(Rows.Count, 1).End(xlUp).Row

'row you want the macro to start on in 'Quotes'
StartRow_Q = 2
'column where your load numbers are in 'Quotes'
Col_Q = 11
'row you want the macro to start on in 'Loads'
StartRow_L = 2
'column where load#'s are in 'Loads'
Col_L = 3

'''''Added by Ben, please uncomment if it is still neccesary
'Loads.Rows("2:" & Loads.Rows.Count).ClearContents

On Error Resume Next

'Look for load# from QUOTES in LOADS, if it doesn't exist in LOADS, add it. (NOT adding rest of the columns, that is done in the next for loop)
For i = StartRow_Q To Quotes.Cells(Rows.Count, Col_Q).End(xlUp).Row + 1

'Quotes.Cells(i, 13).Formula = "=if(h" & i & "<>"""",IF(K" & i & "<>"""",1,0),"")" ' add a 1 or 0 to a help col to calculate quote win %

    If Quotes.Cells(i, 8) <> "" And Quotes.Cells(i, 11) <> "" Then ' if there is a quote and order number ( won the quote)
        Quotes.Cells(i, 13).Value = "1"
    End If

    If Quotes.Cells(i, 8) <> "" And Quotes.Cells(i, 11) = "" Then ' if there is a quote and and no order number (didnt win the quote)
        Quotes.Cells(i, 13).Value = "0"
    End If

    'Load # used to search
    SearchVal = Quotes.Cells(i, Col_Q)
    'MsgBox SearchVal
    Set SearchRange = Loads.Range(Loads.Cells(StartRow_L, 3), Loads.Cells(Rows.Count, 3).End(xlUp))

    'If no match in Loads then write the Load #
    If IsNumeric(Application.Match(SearchVal, SearchRange, 0)) = False Then
    'load #
        N = Loads.Cells(Rows.Count, 3).End(xlUp).Row + 1
        Loads.Unprotect "superior"
        Loads.Range("C" & N) = SearchVal
        'MsgBox N
    End If


Next i

'Search for load#'s in LOADS that don't have info in the rest of the columns, add info for the rest of the columns
For j = StartRow_Q To Loads.Cells(Rows.Count, Col_L).End(xlUp).Row + 1

If Loads.Range("A" & j) = Empty Then

    IRow = 0 'row # in Invoices G:G
    DRow = 0 'row # in Data D:D
    CRow = 0 'row # in Carriers
    DBPRow = 0 'row # in Data BP:BP

    StatusLink = Empty
    StatusText = Empty
    ShipDate = Empty
    OfferedLane = Empty
    Customer = Empty
    Equipment = Empty
    Ratee = Empty
    Profit = Empty
    Carrier = Empty
    CarrierEmail = Empty
    CarrierPhone = Empty
    InvoiceStatus = Empty
    CustomerInvoiced = Empty
    InvoiceAge = Empty
    NewNumber = Empty

    SearchVal = Loads.Range("C" & j)

    IRow = Application.Match(SearchVal, Invoices.Range("G1:G" & InvoicesRow), 0)
    DRow = Application.Match(SearchVal, Data.Range("D1:D" & DataRow), 0)

    If DRow = 0 Then 'Find DRow by looking in Data BP:BP (Assume it is Green Bay Packaging)
        DBPRow = Application.Match(SearchVal, Data.Range("BP1:BP" & DataRow), 0)
        If DBPRow = 0 Then
           'MsgBox "Can't find Load # anywhere, press okay to search for next Load #"
           If j < Loads.Cells(Rows.Count, Col_L).End(xlUp).Row + 1 Then
                GoTo NN
            Else
                Exit For
            End If
        End If
       DRow = Data.Range("D" & DBPRow)
    End If


    If IRow > 0 Then
        'Status Link
        StatusLink = "https://superior.logisticallytms.com/tms/#/3pl/orders/" & SearchVal
        StatusText = Invoices.Range("W" & Row)

        If StatusText = "" Then
            StatusText = "Go To Load"
        End If

        'Ship Date
        ShipDate = Format(CDate(Invoices.Range("K" & IRow)), "mm/dd/yyyy")

        'Offered Lane
       OfferedLane = Invoices.Range("o" & Row) & " " & Invoices.Range("p" & IRow) & " to " & _
            Invoices.Range("s" & Row) & " " & Invoices.Range("t" & IRow)
        'Customer
       Customer = Invoices.Range("B" & IRow)
        'Rate
       Ratee = Format(CLng(Invoices.Range("v" & IRow)), "###0.00")
        'Carrier
       Carrier = Invoices.Range("f" & IRow)
        'Invoice Aging Days
        If Customer = "Belmark, Inc." Then
            InvoiceAge = "Belmark"
        ElseIf CustomerInvoiced <> "YES" Then
            InvoiceAge = CInt(Date - ShipDate) 'in Integers
        End If

        If Customer = "Belmark, Inc." Then
            InvoiceStatus = "Belmark"
            CustomerInvoiced = "Belmark"
        Else
            InvoiceStatus = Invoices.Range("Y" & IRow)
            CustomerInvoiced = Invoices.Range("A" & IRow)
        End If
    ElseIf DRow > 0 Then
        'Status Link
        StatusLink = "https://superior.logisticallytms.com/tms/#/3pl/orders/" & SearchVal
        'look for status text in Data M:M (or Data L:L? not sure which one is the one you want)
        StatusText = Data.Range("M" & DRow)
        'Ship Date
        ShipDate = Format(CDate(Data.Range("N" & DRow)), "mm/dd/yyyy")
        'Offered Lane
       OfferedLane = Data.Range("AR" & DRow) & " " & Data.Range("AT" & DRow) & " to " & _
            Data.Range("BB" & DRow) & " " & Data.Range("BC" & DRow)
        'Customer
       Customer = Data.Range("AB" & DRow)
        'Rate
       Ratee = Format(CLng(Data.Range("AG" & DRow)), "###0.00")
        'Carrier
       Carrier = Data.Range("AD" & DRow)
        'Invoice Aging Days
        If Customer = "Belmark, Inc." Then
            InvoiceAge = "Belmark"
        ElseIf CustomerInvoiced <> "YES" Then
            InvoiceAge = CInt(Date - CDate(Data.Range("N" & DRow))) 'in Integers
        End If
    End If

    'Equipment (returns whatever value is in Data CL:CL)
    Equipment = Data.Range("CL" & DRow)

    'Profit
    If Customer = "Belmark,Inc." Then
        Profit = (Ratee * 1.06) * 1.03 + 1
    Else
        Profit = Data.Range("AK" & DRow)
    End If

    'Search in Carriers sheet
    CRow = Application.Match(Carrier, Carriers.Range("D1:D" & CarriersRow), 0)

    If CRow > 0 Then
        'Carrier Email
       CarrierEmail = Carriers.Range("M" & CRow)
        'Carrier Phone Number
       CarrierPhone = Carriers.Range("N" & CRow)
    End If


    'Write everything in the sheet
    Loads.Hyperlinks.Add Anchor:=Loads.Range("B" & j), Address:=StatusLink, TextToDisplay:=StatusText
    Loads.Range("D" & j) = ShipDate
    Loads.Range("E" & j) = OfferedLane
    Loads.Range("F" & j) = Customer
    Loads.Range("G" & j) = Equipment
    Loads.Range("H" & j) = Ratee
    Loads.Range("I" & j) = Profit
    Loads.Range("J" & j) = Carrier
    Loads.Range("K" & j) = CarrierEmail
    Loads.Range("L" & j) = CarrierPhone
    Loads.Range("M" & j) = InvoiceStatus
    Loads.Range("N" & j) = CustomerInvoiced
    Loads.Range("O" & j) = InvoiceAge

    'Email
    If StatusText = "Go To Load" Then
        Loads.Range("A" & j) = ""
    Else
        If IsEmpty(ShipDate) = False And IsEmpty(OfferedLane) = False And IsEmpty(CarrierEmail) = False Then
            Loads.Range("A" & j) = "Send Email"
        End If
    End If


End If
NN:
Next j


Worksheets("Quotes").Activate


' add formulas to do city / state lookup based on zip code entered.


For i = Quotes.Cells(Rows.Count, Col_Q).End(xlUp).Row + 1 To Quotes.Cells(Rows.Count, Col_Q).End(xlUp).Row + 49

Quotes.Cells(i, 2).Formula = "=IF(D" & i & "<>"""",INDEX(Zips!$B:$B,MATCH($D" & i & ",Zips!$A:$A,0)),"""")"

Quotes.Cells(i, 3).Formula = "=IF(D" & i & "<>"""",INDEX(Zips!$C:$C,MATCH($D" & i & ",Zips!$A:$A,0)),"""")"

Quotes.Cells(i, 5).Formula = "=IF(G" & i & "<>"""",INDEX(Zips!$B:$B,MATCH($G" & i & ",Zips!$A:$A,0)),"""")"

Quotes.Cells(i, 6).Formula = "=IF(G" & i & "<>"""",INDEX(Zips!$C:$C,MATCH($G" & i & ",Zips!$A:$A,0)),"""")"


Next i



TheWall
Worksheets("Loads").Activate
TheWall


Application.ScreenUpdating = True
SecondsElapsed = Round(Timer - StartTime, 2)


'A little error cleanup
If Rows(Loads.Cells(Rows.Count, 2).End(xlUp).Row) = "to" Then
Rows(Loads.Cells(Rows.Count, 2).End(xlUp).Row).Delete
End If
Quotes.UsedRange.Columns.AutoFit
Quotes.Range("M:M").EntireColumn.Hidden = True

' pop runtime
MsgBox "Loads sheet updated successfully in " & SecondsElapsed & " seconds", vbInformation

On Error GoTo 0

'Lock ranges to prevent users from accidentially deleting stuff

Loads.Range("A:O").Locked = True
Loads.Range("P:P").Locked = False
Loads.Protect "superior"
End Sub