PDA

View Full Version : Range not selecting correctly?


infinity
01-21-2017, 09:07 PM
Hello again everyone,

I am working on a project that is almost complete but I am having an issue that I cannot figure out. I have one block of code that I run and at the end of that code it runs another block of code. Both work fine separately but for some reason when I run them together the second block does not select the correct range. It does select the correct range if I run that part separately though. Here are the two blocks

#1


Option Explicit
Sub Load_Add_New()
'
' New_Load Macro
'


'Message Box if Nothing is on the Line
If Range("C14") = 0 Then
Dim Msg, Style, Title, Response
Msg = "You have not entered a transaction on this line."
Style = vbOKOnly + vbExclamation

Title = "CANNOT ADD A LINE"
Response = MsgBox(["You have not entered a transaction on this line."], vbOKOnly + vbExclamation, ["CANNOT ADD A LINE"])
Exit Sub
End If


'Create New Row
'Application.ScreenUpdating = False
Sheets("Loads Completed").Select
ActiveSheet.unprotect
Range("C13:AR1000").Sort Key1:=Range("F13"), Order1:=xlDescending, _
Key2:=Range("D13"), Order2:=xlAscending, Header:=xlNo


Rows("14:14").Select
Rows("14:14").Insert Shift:=xlDown

'Set Formatting For New Row
Selection.RowHeight = 15
With Selection.Font
.Name = "Calibri"
.Size = 11
End With
With Selection.Font
.ColorIndex = xlAutomatic
.Bold = False
End With

'Set Formatting & Formula For Line Number
Range("A14").Select
Selection.NumberFormat = "0"
Selection.Locked = True
With Selection
.VerticalAlignment = xlCenter
.WrapText = False
.MergeCells = False
End With
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
ActiveCell.FormulaR1C1 = "=R[1]C1+1"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "1"




Dim LR As Long
LR = Range("C14:AI14").End(xlDown).Row


Dim R1 As Range, R2 As Range, MultiRange As Range
Set R1 = Range("C14:AI" & LR)
Set R2 = Range("AK14:AQ" & LR)
Set MultiRange = Union(R1, R2)






MultiRange.Select
Range("C14").Activate


Cells.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MOD($A14,2)=1"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriori ty
With Selection.FormatConditions(1).Interior
.Pattern = xlNone
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.249946592608417
End With
Selection.FormatConditions(1).StopIfTrue = True
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MOD($A14,2)=0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriori ty
With Selection.FormatConditions(1).Interior
.Pattern = xlNone
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = True

'Select B14 to set formatting and Formula
Range("C14").Select
ActiveCell.ClearContents
Range("B14").Select
With Selection
.NumberFormat = "General"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlLeft
.WrapText = False
.MergeCells = False
.Locked = True
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlNone
.Borders(xlEdgeRight).LineStyle = xlNone
End With
ActiveCell.FormulaR1C1 = _
"=IFERROR(IF(RC3<>"""",IF(LEFT(RC3,2)=""DH"",CONCATENATE(RC3,"" "",""Deadhead Miles""),CONCATENATE(RC3,"" "",""Trip Miles"")),""""),"""")"

'Select Load # Field to set formatting
Range("C14").Select
With Selection
.NumberFormat = "General"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlCenter
.WrapText = False
.MergeCells = False
.Locked = False
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlMedium
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With

'Select Pick Up Date Field to set formatting
ActiveCell.Offset(0, 1).Select
With Selection
.NumberFormat = "m-d-yyyy"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlCenter
.WrapText = False
.MergeCells = False
.Locked = False
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With

'Select Delivery Date to set formatting
ActiveCell.Offset(0, 1).Select
With Selection
.NumberFormat = "m-d-yyyy"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlCenter
.WrapText = False
.MergeCells = False
.Locked = False
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With

'Select Settlement Date to set formatting
ActiveCell.Offset(0, 1).Select
With Selection
.NumberFormat = "m-d-yyyy"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlCenter
.WrapText = False
.MergeCells = False
.Locked = False
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With
ActiveCell.FormulaR1C1 = _
"=IFERROR(IF(RC3<>"""",VLOOKUP(RC2,'Settlement Account'!R12C2:R2500C8,7,0),""""),"""")"

'Select "# of Stops" Field to set formatting
ActiveCell.Offset(0, 1).Select
With Selection
.NumberFormat = "0"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlCenter
.WrapText = False
.MergeCells = False
.Locked = False
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With

'Select "Detention Hours" Field to set formatting
ActiveCell.Offset(0, 1).Select
With Selection
.NumberFormat = "0.00"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlCenter
.WrapText = False
.MergeCells = False
.Locked = False
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With


'Select "P/U Customer" Field to set formatting
ActiveCell.Offset(0, 1).Select
With Selection
.NumberFormat = "General"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlLeft
.WrapText = False
.MergeCells = False
.Locked = False
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With

'Select "P/U Location" Field to set formatting
ActiveCell.Offset(0, 1).Select
With Selection
.NumberFormat = "General"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlLeft
.WrapText = False
.MergeCells = False
.Locked = False
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With

'Select "Final Customer" Field to set formatting
ActiveCell.Offset(0, 1).Select
With Selection
.NumberFormat = "General"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlLeft
.WrapText = False
.MergeCells = False
.Locked = False
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With

'Select "Final Location" Field to set formatting
ActiveCell.Offset(0, 1).Select
With Selection
.NumberFormat = "General"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlLeft
.WrapText = False
.MergeCells = False
.Locked = False
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With

'Select "Weight" Field to set formmating
ActiveCell.Offset(0, 1).Select
With Selection
.NumberFormat = "#,##0"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlCenter
.WrapText = False
.MergeCells = False
.Locked = False
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With

'Select "Loaded Miles" Field to set formatting
ActiveCell.Offset(0, 1).Select
With Selection
.NumberFormat = "#,##0"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlCenter
.WrapText = False
.MergeCells = False
.Locked = False
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With

'Select "Empty Miles" Field to set formatting
ActiveCell.Offset(0, 1).Select
With Selection
.NumberFormat = "#,##0"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlCenter
.WrapText = False
.MergeCells = False
.Locked = False
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With

'Select "Total Miles" Field to set formatting
ActiveCell.Offset(0, 1).Select
With Selection
.NumberFormat = "#,##0"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlCenter
.WrapText = False
.MergeCells = False
.Locked = True
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With
ActiveCell.FormulaR1C1 = "=IF(OR(RC14<>"""",RC15<>""""),SUM(RC14,RC15),"""")"

'Select "Actual Miles" Field to set formatting
ActiveCell.Offset(0, 1).Select
With Selection
.NumberFormat = "#,##0.0"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlCenter
.WrapText = False
.MergeCells = False
.Locked = False
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With

'Select "Difference" Field to set formatting
ActiveCell.Offset(0, 1).Select
With Selection
.NumberFormat = "0.0%"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlCenter
.WrapText = False
.MergeCells = False
.Locked = True
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With
ActiveCell.FormulaR1C1 = "=IF(AND(RC16<>"""",RC17<>""""),1-RC16/RC17,"""")"

'Select "Loaded Rate" Field to set formatting
ActiveCell.Offset(0, 1).Select
With Selection
.NumberFormat = "_($* #,##0.000_);_($* (#,##0.000);_($* ""-""???_);_(@_)"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlGeneral
.WrapText = False
.MergeCells = False
.Locked = True
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With
ActiveCell.FormulaR1C1 = _
"=IF(AND(RC3<>"""",RC14<>""""),IF(AND(RC14<800,RC3<>""""),VLOOKUP(RC14,'Pay Rates'!R2C1:R802C3,MATCH(R13C,'Pay Rates'!R1C1:R1C3,0),0),VLOOKUP(800,'Pay Rates'!R2C1:R802C3,MATCH(R13C,'Pay Rates'!R1C1:R1C3,0),0)),"""")"

'Select "Empty Rate" Field to set formatting
ActiveCell.Offset(0, 1).Select
With Selection
.NumberFormat = "_($* #,##0.000_);_($* (#,##0.000);_($* ""-""???_);_(@_)"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlGeneral
.WrapText = False
.MergeCells = False
.Locked = True
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With
ActiveCell.FormulaR1C1 = _
"=IF(AND(RC3<>"""",RC15<>""""),VLOOKUP(RC15,'Pay Rates'!R2C1:R802C3,MATCH(R13C,'Pay Rates'!R1C1:R1C3,0),0),"""")"

'Select "Gallons Used" Field to set formatting
ActiveCell.Offset(0, 1).Select
With Selection
.NumberFormat = "0.0"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlCenter
.WrapText = False
.MergeCells = False
.Locked = False
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With

'Select "Idle Gallons" Field to set formatting
ActiveCell.Offset(0, 1).Select
With Selection
.NumberFormat = "0.0"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlCenter
.WrapText = False
.MergeCells = False
.Locked = False
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With

'Select "Driving Gallons" Field to set formatting
ActiveCell.Offset(0, 1).Select
With Selection
.NumberFormat = "0.0"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlCenter
.WrapText = False
.MergeCells = False
.Locked = True
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With
ActiveCell.FormulaR1C1 = "=IF(RC21<>"""",RC21-RC22,"""")"

'Select "Driving MPG" Field to set formatting
ActiveCell.Offset(0, 1).Select
With Selection
.NumberFormat = "0.0"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlCenter
.WrapText = False
.MergeCells = False
.Locked = True
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With
ActiveCell.FormulaR1C1 = "=IF(AND(RC17<>"""",RC23<>""""),RC17/RC23,"""")"

'Select "Overall MPG" Field to set formatting
ActiveCell.Offset(0, 1).Select
With Selection
.NumberFormat = "0.0"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlCenter
.WrapText = False
.MergeCells = False
.Locked = True
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With
ActiveCell.FormulaR1C1 = "=IF(AND(RC17<>"""",RC21<>""""),RC17/RC21,"""")"

'Select "Cost Per Gallon" Field to set formatting
ActiveCell.Offset(0, 1).Select
With Selection
.NumberFormat = "_($* #,##0.000_);_($* (#,##0.000);_($* ""-""???_);_(@_)"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlGeneral
.WrapText = False
.MergeCells = False
.Locked = False
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With

'Select "Fuel Cost Driving" Field to set formatting
ActiveCell.Offset(0, 1).Select
With Selection
.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlGeneral
.WrapText = False
.MergeCells = False
.Locked = True
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With
ActiveCell.FormulaR1C1 = "=IF(AND(RC23<>"""",RC26<>""""),RC23*RC26,"""")"

'Select "Fuel Cost Idling" Field to set formatting
ActiveCell.Offset(0, 1).Select
With Selection
.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlGeneral
.WrapText = False
.MergeCells = False
.Locked = True
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With
ActiveCell.FormulaR1C1 = "=IF(AND(RC22<>"""",RC26<>""""),RC22*RC26,"""")"

'Select "Total Fuel Cost" Field to set formatting
ActiveCell.Offset(0, 1).Select
With Selection
.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlGeneral
.WrapText = False
.MergeCells = False
.Locked = True
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With
ActiveCell.FormulaR1C1 = "=IF(RC27<>"""",SUM(RC27,RC28),"""")"


'Select "Loaded Revenue" Field to set formatting
ActiveCell.Offset(0, 1).Select
With Selection
.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlGeneral
.WrapText = False
.MergeCells = False
.Locked = True
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With
ActiveCell.FormulaR1C1 = "=IF(AND(RC3<>"""",RC14<>""""),RC14*RC19,"""")"


'Select "Empty Revenue" Field to set formatting
ActiveCell.Offset(0, 1).Select
With Selection
.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlGeneral
.WrapText = False
.MergeCells = False
.Locked = True
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With
ActiveCell.FormulaR1C1 = "=IF(AND(RC3<>"""",RC15<>""""),RC15*RC20,"""")"

'Select "Extra Stop Revenue" Field to set formatting
ActiveCell.Offset(0, 1).Select
With Selection
.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlGeneral
.WrapText = False
.MergeCells = False
.Locked = True
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With
ActiveCell.FormulaR1C1 = "=IF(AND(RC3<>"""",RC7<>""""),RC7*35,"""")"

'Select "Detention Revenue" Field to set formatting
ActiveCell.Offset(0, 1).Select
With Selection
.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlGeneral
.WrapText = False
.MergeCells = False
.Locked = True
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With
ActiveCell.FormulaR1C1 = "=IF(AND(RC3<>"""",RC8<>""""),RC8*30,"""")"

'Select "Total Revenue" Field to set formatting
ActiveCell.Offset(0, 1).Select
With Selection
.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlGeneral
.WrapText = False
.MergeCells = False
.Locked = True
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With
ActiveCell.FormulaR1C1 = "=IF(AND(RC14<>"""",RC19<>""""),SUM(RC30:RC33),"""")"

'Select "Net" Field to set formatting
ActiveCell.Offset(0, 1).Select
With Selection
.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlGeneral
.WrapText = False
.MergeCells = False
.Locked = True
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlMedium
End With
ActiveCell.FormulaR1C1 = "=IF(AND(RC29<>"""",RC34<>""""),RC34-RC29,"""")"

'Selects "Amount Paid" to set formatting and formula
ActiveCell.Offset(0, 2).Select
With Selection
.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlGeneral
.WrapText = False
.MergeCells = False
.Locked = True
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlMedium
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With
ActiveCell.FormulaR1C1 = "=SUM(RC46:RC49)"

'Selects "Difference" to set formatting and formula
ActiveCell.Offset(0, 1).Select
With Selection
.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlGeneral
.WrapText = False
.MergeCells = False
.Locked = True
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With
ActiveCell.FormulaR1C1 = "=IF(AND(RC34<>"""",RC37<>""""),RC37-RC34,"""")"


'Selects "Cost By Run" to set formatting and formula
ActiveCell.Offset(0, 1).Select
With Selection
.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlGeneral
.WrapText = False
.MergeCells = False
.Locked = True
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With
ActiveCell.FormulaR1C1 = _
"=IFERROR(IF(AND(RC3<>"""",RC16<>""""),SUMIFS('Settlement Account'!R12C6:R5000C6,'Settlement Account'!R12C8:R5000C8,RC6,'Settlement Account'!R12C3:R5000C3,""Settlement"",'Settlement Account'!R12C12:R5000C12,""Fixed Cost"")*(RC16/SUMIF(R13C6:R191C6,RC6,R13C16:R191C16)),0),0)"

'Selects "CPM By Run" to set formatting and formula
ActiveCell.Offset(0, 1).Select
With Selection
.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlGeneral
.WrapText = False
.MergeCells = False
.Locked = True
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With
ActiveCell.FormulaR1C1 = "=IF(AND(RC3<>"""",RC39<>""""),(RC37-RC39)/RC16,0)"

'Selects "Additional Revenue" to set formatting and formula
ActiveCell.Offset(0, 1).Select
With Selection
.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlGeneral
.WrapText = False
.MergeCells = False
.Locked = True
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With
ActiveCell.FormulaR1C1 = "=IF(RC3<>"""",SUM(RC50:RC52),0)"

'Selects "Total CPM by Run" to set formatting and formula
ActiveCell.Offset(0, 1).Select
With Selection
.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlGeneral
.WrapText = False
.MergeCells = False
.Locked = True
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With
ActiveCell.FormulaR1C1 = "=IF(RC3<>"""",(SUM(RC37,RC41)-RC39)/RC16,0)"

'Selects "" to set formatting and formula
ActiveCell.Offset(0, 1).Select
With Selection
.NumberFormat = "General"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlCenter
.WrapText = False
.MergeCells = False
.Locked = False
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlMedium
End With

'Selects "Deadhead Revenue" to set formula
ActiveCell.Offset(0, 3).Select
With Selection
.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlGeneral
.WrapText = False
.MergeCells = False
.Locked = True
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlNone
.Borders(xlEdgeRight).LineStyle = xlNone
End With
ActiveCell.FormulaR1C1 = _
"=IF((LEFT(RC3,2)=""DH""),IFERROR(VLOOKUP(CONCATENATE(RC3,"" "",""Deadhead Miles""),'Settlement Account'!R12C2:R5000C14,MATCH(""ADDED REVENUE"",'Settlement Account'!R11C2:R11C14,0),0),""""),"""")"


'Selects "Trip Revenue" to set formula
ActiveCell.Offset(0, 1).Select
With Selection
.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlGeneral
.WrapText = False
.MergeCells = False
.Locked = True
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlNone
.Borders(xlEdgeRight).LineStyle = xlNone
End With
ActiveCell.FormulaR1C1 = _
"=IFERROR(IF(AND(RC3<>"""",RC14<>""""),VLOOKUP(CONCATENATE(RC3,"" "",""Trip Miles""),'Settlement Account'!R12C2:R5000C14,MATCH(""ADDED REVENUE"",'Settlement Account'!R11C2:R11C14,0),0),""""),"""")"


'Selects "Extra Stop Revenue" to set formula
ActiveCell.Offset(0, 1).Select
With Selection
.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlGeneral
.WrapText = False
.MergeCells = False
.Locked = True
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlNone
.Borders(xlEdgeRight).LineStyle = xlNone
End With
ActiveCell.FormulaR1C1 = _
"=IFERROR(IF(AND(RC3<>"""",RC7<>""""),VLOOKUP(CONCATENATE(RC3,"" "",""Extra Stop Pay""),'Settlement Account'!R12C2:R5000C14,MATCH(""ADDED REVENUE"",'Settlement Account'!R11C2:R11C14,0),0),""""),"""")"


'Selects "Detention Revenue" to set formula
ActiveCell.Offset(0, 1).Select
With Selection
.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlGeneral
.WrapText = False
.MergeCells = False
.Locked = True
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlNone
.Borders(xlEdgeRight).LineStyle = xlNone
End With
ActiveCell.FormulaR1C1 = _
"=IFERROR(IF(AND(RC3<>"""",RC8<>""""),VLOOKUP(CONCATENATE(RC3,"" "",""Detention Pay""),'Settlement Account'!R12C2:R5000C14,MATCH(""ADDED REVENUE"",'Settlement Account'!R11C2:R11C14,0),0),""""),"""")"

'Selects "Additional Revenue" to set formula
ActiveCell.Offset(0, 1).Select
With Selection
.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlGeneral
.WrapText = False
.MergeCells = False
.Locked = True
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlNone
.Borders(xlEdgeRight).LineStyle = xlNone
End With
ActiveCell.FormulaR1C1 = _
"=IF(RC[-47]<>"""",SUMIFS('Settlement Account'!R12C7:R5000C7,'Settlement Account'!R12C11:R5000C11,""Revenue"",'Settlement Account'!R12C8:R5000C8,R14C6:R190C6,'Settlement Account'!R12C5:R5000C5,R14C3:R190C3)-RC37,0)"

'Selects "Draws Allocated by Mile" to set formula
ActiveCell.Offset(0, 1).Select
With Selection
.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlGeneral
.WrapText = False
.MergeCells = False
.Locked = True
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlNone
.Borders(xlEdgeRight).LineStyle = xlNone
End With
ActiveCell.FormulaR1C1 = _
"=IF(AND(RC3<>"""",RC16=0),SUMIFS('Settlement Account'!R12C7:R5000C7,'Settlement Account'!R12C12:R5000C12,""Draw"",'Settlement Account'!R12C8:R5000C8,RC6),IF(AND(RC3<>"""",RC16<>0),SUMIFS('Settlement Account'!R12C7:R5000C7,'Settlement Account'!R12C12:R5000C12,""Draw"",'Settlement Account'!R12C8:R5000C8,RC6)*(RC16/SUMIF(R14C6:R190C6,RC6,R14C16:R190C16)),0))"


'Selects "Previous Settlement Balance" to set formula
ActiveCell.Offset(0, 1).Select
With Selection
.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlGeneral
.WrapText = False
.MergeCells = False
.Locked = True
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlNone
.Borders(xlEdgeRight).LineStyle = xlNone
End With
ActiveCell.FormulaR1C1 = _
"=IF(AND(RC3<>0,RC16=0),0-SUMIFS('Settlement Account'!R12C6:R5000C6,'Settlement Account'!R12C10:R5000C10,""Settle Previous Negative Balance"",'Settlement Account'!R12C8:R5000C8,RC6),IF(AND(RC[-49]<>0,RC[-36]<>0),0-SUMIFS('Settlement Account'!R12C6:R5000C6,'Settlement Account'!R12C10:R5000C10,""Settle Previous Negative Balance"",'Settlement Account'!R12C8:R5000C8,RC6)*RC16/(SUMIF(R14C6:R190C6,RC6,R14C16:R190C16)),0))"

Sheets("Loads Completed").Select
Application.ScreenUpdating = True
Range("C14").Select
Application.ScreenUpdating = False
ActiveSheet.Protect
Range("C14").Select
Application.CutCopyMode = False
Application.Run "'Owner Operater Run Log.xlsm'!Load_Analysis"

End Sub


#2


Option Explicit
Sub Load_Analysis()
'
' Macro1 Macro
'


'

Dim lastRow As Long
lastRow = Range("$B$7:$Y$7").End(xlDown).Row

Dim rangeTwo As Range

Sheets("Load Analysis").Select
ActiveSheet.unprotect
'Application.ScreenUpdating = False
Range("A7").Select

Do
Range("A7").Select
If ActiveCell.Offset(0, 0).Range("A1") <> Sheets("Loads Completed").Range("A14") Then
Rows("7:7").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Range("B7:Y7").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Font
.ColorIndex = xlAutomatic
.TintAndShade = 0
End With
Selection.Font.Bold = False
Rows("7:7").RowHeight = 15

'Selects "Number Column" to set format and formula
Range("A7").Select
With Selection
.NumberFormat = "0"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlCenter
.WrapText = False
.MergeCells = False
.Locked = True
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlNone
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlMedium
End With
ActiveCell.FormulaR1C1 = "=R[1]C1+1"
Range("B7").Select
End If
Loop While ActiveCell.Offset(0, 0) <> Sheets("Loads Completed").Range("A14")


'Selects "Total Miles" to set format and formula
ActiveCell.Offset(0, 2).Select
With Selection
.NumberFormat = "#,##0"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlCenter
.WrapText = False
.MergeCells = False
.Locked = True
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With
ActiveCell.FormulaR1C1 = _
"=IFERROR(IF(RC2<>"""",VLOOKUP(RC2,'Loads Completed'!R14C3:R500C44,MATCH(R6C,'Loads Completed'!R13C3:R13C44,0),0),""""),"""")"

'Selects "Percentage of Miles" to set format and formula
ActiveCell.Offset(0, 1).Select
With Selection
.NumberFormat = "0.00%"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlCenter
.WrapText = False
.MergeCells = False
.Locked = True
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With
ActiveCell.FormulaR1C1 = "=IFERROR(IF(RC2<>"""",RC3/SUMIF(R7C5:R500C5,RC5,R7C3:R500C3),""""),"""")"

'Selects "Settlement Date" to set format and formula
ActiveCell.Offset(0, 1).Select
With Selection
.NumberFormat = "M-D-YYYY"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlCenter
.WrapText = False
.MergeCells = False
.Locked = True
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With
ActiveCell.FormulaR1C1 = _
"=IFERROR(IF(RC2<>"""",VLOOKUP(RC2,'Loads Completed'!R14C3:R500C44,MATCH(R6C,'Loads Completed'!R13C3:R13C44,0),0),""""),"""")"

'Selects "Revenue Amount" to set format and formula
ActiveCell.Offset(0, 1).Select
With Selection
.NumberFormat = "_($* #,##0.000_);_($* (#,##0.000);_($* ""-""???_);_(@_)"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlGeneral
.WrapText = False
.MergeCells = False
.Locked = True
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With
ActiveCell.FormulaR1C1 = _
"=IFERROR(IF(RC2<>"""",SUM(SUMIFS('Settlement Account'!R13C7:R5000C7,'Settlement Account'!R13C5:R5000C5,RC2,'Settlement Account'!R13C11:R5000C11,""Revenue""),SUMIFS('Settlement Account'!R13C7:R5000C7,'Settlement Account'!R13C8:R5000C8,RC5,'Settlement Account'!R13C10:R5000C10,""Revenue Draw"")*(RC3/SUMIF(R7C5:R500C5,RC5,R7C3:R500C3))),""""),"""")"

'Selects "Revenue CPM" to set format and formula
ActiveCell.Offset(0, 1).Select
With Selection
.NumberFormat = "_($* #,##0.000_);_($* (#,##0.000);_($* ""-""???_);_(@_)"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlGeneral
.WrapText = False
.MergeCells = False
.Locked = True
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With
ActiveCell.FormulaR1C1 = "=IFERROR(IF(RC2<>"""",RC6/RC3,""""),"""")"

'Selects "Truck Payment Amount" to set format and formula
ActiveCell.Offset(0, 1).Select
With Selection
.NumberFormat = "_($* #,##0.000_);_($* (#,##0.000);_($* ""-""???_);_(@_)"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlGeneral
.WrapText = False
.MergeCells = False
.Locked = True
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With
ActiveCell.FormulaR1C1 = _
"=IFERROR(IF(RC2<>"""",SUMIFS('Settlement Account'!R13C6:R5000C6,'Settlement Account'!R13C8:R5000C8,RC5,'Settlement Account'!R13C10:R5000C10,R5C)*(RC3/SUMIF(R7C5:R185C5,RC5,R7C3:R185C3)),""""),"""")"

'Selects "Truck Payment CPM" to set format and formula
ActiveCell.Offset(0, 1).Select
With Selection
.NumberFormat = "_($* #,##0.000_);_($* (#,##0.000);_($* ""-""???_);_(@_)"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlGeneral
.WrapText = False
.MergeCells = False
.Locked = True
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With
ActiveCell.FormulaR1C1 = "=IFERROR(IF(RC2<>"""",RC8/RC3,""""),"""")"

'Selects "Diesel Fuel Amount" to set format and formula
ActiveCell.Offset(0, 1).Select
With Selection
.NumberFormat = "_($* #,##0.000_);_($* (#,##0.000);_($* ""-""???_);_(@_)"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlGeneral
.WrapText = False
.MergeCells = False
.Locked = True
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With
ActiveCell.FormulaR1C1 = _
"=IFERROR(IF(RC2<>"""",SUMIFS('Settlement Account'!R13C6:R5000C6,'Settlement Account'!R13C8:R5000C8,RC5,'Settlement Account'!R13C10:R5000C10,R5C)*(RC3/SUMIF(R7C5:R185C5,RC5,R7C3:R185C3)),""""),"""")"

'Selects "Diesel Fuel CPM" to set format and formula
ActiveCell.Offset(0, 1).Select
With Selection
.NumberFormat = "_($* #,##0.000_);_($* (#,##0.000);_($* ""-""???_);_(@_)"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlGeneral
.WrapText = False
.MergeCells = False
.Locked = True
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With
ActiveCell.FormulaR1C1 = "=IFERROR(IF(RC2<>"""",RC10/RC3,""""),"""")"

'Selects "Computer Rental Amount" to set format and formula
ActiveCell.Offset(0, 1).Select
With Selection
.NumberFormat = "_($* #,##0.000_);_($* (#,##0.000);_($* ""-""???_);_(@_)"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlGeneral
.WrapText = False
.MergeCells = False
.Locked = True
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With
ActiveCell.FormulaR1C1 = _
"=IFERROR(IF(RC2<>"""",SUMIFS('Settlement Account'!R13C6:R5000C6,'Settlement Account'!R13C8:R5000C8,RC5,'Settlement Account'!R13C10:R5000C10,R5C)*(RC3/SUMIF(R7C5:R185C5,RC5,R7C3:R185C3)),""""),"""")"

'Selects "Computer Rental CPM" to set format and formula
ActiveCell.Offset(0, 1).Select
With Selection
.NumberFormat = "_($* #,##0.000_);_($* (#,##0.000);_($* ""-""???_);_(@_)"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlGeneral
.WrapText = False
.MergeCells = False
.Locked = True
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With
ActiveCell.FormulaR1C1 = "=IFERROR(IF(RC2<>"""",RC12/RC3,""""),"""")"

'Selects "Insurance Amount" to set format and formula
ActiveCell.Offset(0, 1).Select
With Selection
.NumberFormat = "_($* #,##0.000_);_($* (#,##0.000);_($* ""-""???_);_(@_)"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlGeneral
.WrapText = False
.MergeCells = False
.Locked = True
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With
ActiveCell.FormulaR1C1 = _
"=IFERROR(IF(RC2<>"""",SUMIFS('Settlement Account'!R13C6:R5000C6,'Settlement Account'!R13C8:R5000C8,RC5,'Settlement Account'!R13C10:R5000C10,R5C)*(RC3/SUMIF(R7C5:R185C5,RC5,R7C3:R185C3)),""""),"""")"

'Selects "Insurance CPM" to set format and formula
ActiveCell.Offset(0, 1).Select
With Selection
.NumberFormat = "_($* #,##0.000_);_($* (#,##0.000);_($* ""-""???_);_(@_)"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlGeneral
.WrapText = False
.MergeCells = False
.Locked = True
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With
ActiveCell.FormulaR1C1 = "=IFERROR(IF(RC2<>"""",RC12/RC3,""""),"""")"

'Selects "Maintenance Account Amount" to set format and formula
ActiveCell.Offset(0, 1).Select
With Selection
.NumberFormat = "_($* #,##0.000_);_($* (#,##0.000);_($* ""-""???_);_(@_)"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlGeneral
.WrapText = False
.MergeCells = False
.Locked = True
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With
ActiveCell.FormulaR1C1 = _
"=IFERROR(IF(RC2<>"""",SUMIFS('Settlement Account'!R13C6:R5000C6,'Settlement Account'!R13C8:R5000C8,RC5,'Settlement Account'!R13C11:R5000C11,R5C)*(RC3/SUMIF(R7C5:R185C5,RC5,R7C3:R185C3)),""""),"""")"

'Selects "Maintenance Account CPM" to set format and formula
ActiveCell.Offset(0, 1).Select
With Selection
.NumberFormat = "_($* #,##0.000_);_($* (#,##0.000);_($* ""-""???_);_(@_)"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlGeneral
.WrapText = False
.MergeCells = False
.Locked = True
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With
ActiveCell.FormulaR1C1 = "=IFERROR(IF(RC2<>"""",RC16/RC3,""""),"""")"

'Selects "Tolls Amount" to set format and formula
ActiveCell.Offset(0, 1).Select
With Selection
.NumberFormat = "_($* #,##0.000_);_($* (#,##0.000);_($* ""-""???_);_(@_)"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlGeneral
.WrapText = False
.MergeCells = False
.Locked = True
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With
ActiveCell.FormulaR1C1 = _
"=IFERROR(IF(RC2<>"""",SUMIFS('Settlement Account'!R13C6:R5000C6,'Settlement Account'!R13C8:R5000C8,RC5,'Settlement Account'!R13C11:R5000C11,R5C)*(RC3/SUMIF(R7C5:R185C5,RC5,R7C3:R185C3)),""""),"""")"

'Selects "Tolls CPM" to set format and formula
ActiveCell.Offset(0, 1).Select
With Selection
.NumberFormat = "_($* #,##0.000_);_($* (#,##0.000);_($* ""-""???_);_(@_)"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlGeneral
.WrapText = False
.MergeCells = False
.Locked = True
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With
ActiveCell.FormulaR1C1 = "=IFERROR(IF(RC2<>"""",RC18/RC3,""""),"""")"

'Selects "Taxes Amount" to set format and formula
ActiveCell.Offset(0, 1).Select
With Selection
.NumberFormat = "_($* #,##0.000_);_($* (#,##0.000);_($* ""-""???_);_(@_)"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlGeneral
.WrapText = False
.MergeCells = False
.Locked = True
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With
ActiveCell.FormulaR1C1 = _
"=IFERROR(IF(RC2<>"""",SUMIFS('Settlement Account'!R13C6:R5000C6,'Settlement Account'!R13C8:R5000C8,RC5,'Settlement Account'!R13C11:R5000C11,R5C)*(RC3/SUMIF(R7C5:R185C5,RC5,R7C3:R185C3)),""""),"""")"

'Selects "Taxes CPM" to set format and formula
ActiveCell.Offset(0, 1).Select
With Selection
.NumberFormat = "_($* #,##0.000_);_($* (#,##0.000);_($* ""-""???_);_(@_)"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlGeneral
.WrapText = False
.MergeCells = False
.Locked = True
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With
ActiveCell.FormulaR1C1 = "=IFERROR(IF(RC2<>"""",RC20/RC3,""""),"""")"

'Selects "Total Cost Amount" to set format and formula
ActiveCell.Offset(0, 1).Select
With Selection
.NumberFormat = "_($* #,##0.000_);_($* (#,##0.000);_($* ""-""???_);_(@_)"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlGeneral
.WrapText = False
.MergeCells = False
.Locked = True
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With
ActiveCell.FormulaR1C1 = _
"=IFERROR(IF(RC2<>"""",SUM(RC8,RC10,RC12,RC14,RC16,RC18,RC20),""""),"""")"

'Selects "Total Cost CPM" to set format and formula
ActiveCell.Offset(0, 1).Select
With Selection
.NumberFormat = "_($* #,##0.000_);_($* (#,##0.000);_($* ""-""???_);_(@_)"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlGeneral
.WrapText = False
.MergeCells = False
.Locked = True
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With
ActiveCell.FormulaR1C1 = _
"=IFERROR(IF(RC2<>"""",SUM(RC9,RC11,RC13,RC15,RC17,RC19,RC21),""""),"""")"

'Selects "Net Profit Amount" to set format and formula
ActiveCell.Offset(0, 1).Select
With Selection
.NumberFormat = "_($* #,##0.000_);_($* (#,##0.000);_($* ""-""???_);_(@_)"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlGeneral
.WrapText = False
.MergeCells = False
.Locked = True
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
End With
ActiveCell.FormulaR1C1 = "=IFERROR(IF(RC2<>"""",RC6-RC22,""""),"""")"

'Selects "Net Profit CPM" to set format and formula
ActiveCell.Offset(0, 1).Select
With Selection
.NumberFormat = "_($* #,##0.000_);_($* (#,##0.000);_($* ""-""???_);_(@_)"
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlGeneral
.WrapText = False
.MergeCells = False
.Locked = True
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlMedium
End With
ActiveCell.FormulaR1C1 = "=IFERROR(IF(RC2<>"""",RC24/RC3,""""),"""")"
Range("H7").Select
Range("A7").Select
If ActiveCell.Offset(1, 1) = "" Then
Range("C7:Y7").Select
Selection.Copy
Do
If ActiveCell.Offset(0, -2) <> 1 Then
ActiveCell.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End If
Loop Until ActiveCell.Offset(0, -2) = 1
End If
Sheets("Loads Completed").Select
Range("I14").Select
Range("C15").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Load Analysis").Select
Range("B8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B7").Select
ActiveCell.FormulaR1C1 = _
"=IF('Loads Completed'!R[7]C3<>"""",'Loads Completed'!R[7]C3,"""")"

Set rangeTwo = Range("$B$7:$Y$" & lastRow)
rangeTwo.Select

Cells.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MOD($A7,2)=1"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriori ty
With Selection.FormatConditions(1)
.Font.ColorIndex = xlAutomatic
.Font.TintAndShade = 0
.Borders(xlTop).LineStyle = xlNone
.Borders(xlBottom).LineStyle = xlNone
.Interior.Pattern = xlNone
.Interior.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = True
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MOD($A7,2)=0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriori ty
With Selection.FormatConditions(1)
.Font.ColorIndex = xlAutomatic
.Font.TintAndShade = 0
.Borders(xlTop).LineStyle = xlNone
.Borders(xlBottom).LineStyle = xlNone
.Interior.PatternColorIndex = xlAutomatic
.Interior.ThemeColor = xlThemeColorDark1
.Interior.TintAndShade = -0.249946592608417
End With
Selection.FormatConditions(1).StopIfTrue = True
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND($A6<>"""",$E7<>$E6)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriori ty
With Selection.FormatConditions(1)
.Borders(xlTop).LineStyle = xlContinuous
.Borders(xlTop).Weight = xlThin
.Borders(xlTop).Color = -16776961
.Borders(xlTop).TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("B7").Select
ActiveSheet.Protect





End Sub


When the code runs through the "Sub Load Analysis" as part of the "Sub Load_Add_New" when it selects the range declared variable range "rangeTwo" it only selects the first 10 lines and this sheet currently has 17 lines on it, it should be selecting all 17. When I run this code by itself it selects the range correctly. I am thinking that something is getting confused between the two blocks of code but I cannot find it for the life of me. Can anyone help? Thank you in advance.
SamT
01-21-2017, 10:07 PM
Why don't you clean up those Macros and turn them into real Code first?
offthelip
01-22-2017, 04:59 AM
The last line of the first macro is:
Application.Run "'Owner Operater Run Log.xlsm'!Load_Analysis"


which implies the second macro is in a different workbook,

At the start of the second macro you have:

Sub Load_Analysis()
'
' Macro1 Macro
'


'

Dim lastRow As Long
lastRow = Range("$B$7:$Y$7").End(xlDown).Row

Dim rangeTwo As Range

Sheets("Load Analysis").Select


So the last row calculation does it's calculation on whatever workbook/worksheet the last macro left it on. I suspect your error is because you are not correctly selecting the workbook and worksheet at the start of the second macro.