Please take a look at this code.
Why is is stopping and throwing an error?
the Blue End if is where it stops, ( for no good reason ).
Private Sub Format()
Application.EnableEvents = False
Application.ScreenUpdating = False
With Sheets("Sheet1")
FR = 2
LR = .Cells(Rows.Count, "A").End(xlUp).Row
'New Headers
.Range("A1:Q1") = Array("Customer", "City", "State", "Order #", "PO #", "Type", "Pro #", "Cases", "Pounds", "WHS", "Carrier", "Promise Date", "Ship Date", "Status", "Header Hold", "Detail Hold", "In Database")
'Delete Closed or Cancelled Orders
For x = LR To FR Step -1
If .Cells(x, "N") <> "OPEN" Then
.Rows(x).EntireRow.Delete
End If
Next x
'Delete On Hold Orders
For x = LR To FR Step -1
If .Cells(x, "O") <> 0 Or .Cells(x, "P") <> 0 Then
.Rows(x).EntireRow.Delete
End If
Next x
'Convert Order# to Number
Columns("D:D").Select
Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
'Enter Vlookup's
.Range("Q" & FR & ":Q" & LR).Formula = "=IF(ISERROR(VLOOKUP(D2,Database!D:E,2,FALSE)),""NO"",VLOOKUP(D2,Database!D:E,2,FALSE))"
'Copy/Paste Vlookup Results
Columns("Q:Q").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
End Sub
Why is is stopping and throwing an error?
the Blue End if is where it stops, ( for no good reason ).
Private Sub Format()
Application.EnableEvents = False
Application.ScreenUpdating = False
With Sheets("Sheet1")
FR = 2
LR = .Cells(Rows.Count, "A").End(xlUp).Row
'New Headers
.Range("A1:Q1") = Array("Customer", "City", "State", "Order #", "PO #", "Type", "Pro #", "Cases", "Pounds", "WHS", "Carrier", "Promise Date", "Ship Date", "Status", "Header Hold", "Detail Hold", "In Database")
'Delete Closed or Cancelled Orders
For x = LR To FR Step -1
If .Cells(x, "N") <> "OPEN" Then
.Rows(x).EntireRow.Delete
End If
Next x
'Delete On Hold Orders
For x = LR To FR Step -1
If .Cells(x, "O") <> 0 Or .Cells(x, "P") <> 0 Then
.Rows(x).EntireRow.Delete
End If
Next x
'Convert Order# to Number
Columns("D:D").Select
Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
'Enter Vlookup's
.Range("Q" & FR & ":Q" & LR).Formula = "=IF(ISERROR(VLOOKUP(D2,Database!D:E,2,FALSE)),""NO"",VLOOKUP(D2,Database!D:E,2,FALSE))"
'Copy/Paste Vlookup Results
Columns("Q:Q").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
End Sub