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
#2Option 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).SetFirstPriority 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).SetFirstPriority 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
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.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).SetFirstPriority 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).SetFirstPriority 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).SetFirstPriority 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
Reply With Quote