The exact error message is:

Compile Error
Expected End Sub

"jmdaniel" wrote:

Okay, that makes sense. I inserted your stuff in there, but now am getting an error message, "expecting a sub" with the 5th line below highlighted:

Selection.Sort Key1:=Range("O2"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Columns("O:O").Select
Selection.TextToColumns Destination:=Range("O1"), DataType:=xlFixedWidth, _
OtherChar:="|", FieldInfo:=Array(Array(0, 2), Array(7, 1)), _
TrailingMinusNumbers:=True
Sub tester()
Dim lstRow As Long
Let lstRow = Range("a65536").End(xlUp).Row
If lstRow 2 Then
Range(Cells(2, 16), Cells(lstRow, 16)).FormulaR1C1 = _
"=IF(RC[-1]=""H"",""On Time"",IF(RC[-1]=""M"",CHOOSE((RC[-2]=0)+1,""Late"",""Early""),""""))"
Else: Cells(2, 16).FormulaR1C1 = _
"=IF(RC[-1]=""H"",""On Time"",IF(RC[-1]=""M"",CHOOSE((RC[-2]=0)+1,""Late"",""Early""),""""))"
End If
End Sub

End Sub

This section of the macro simply removes the leading blank spaces before the letter "M", (the report being worked on is pulled from Oracle originally). If I don't do that, the macro returns incorrect values in the P column. With this error in place, I don't get down to your code to see if it works. Thanks!



"Nate Oliver" wrote:

Hello Jeff,

I took a peek at Nate's response, but can't really tell what it is supposed to do. Thanks!

Step by step. It stores the last used row based on column A's data. It then looks at which row that is. If the row is greater then 2, it takes the range p2:px where x is your last row and populates it with the following function:

=IF(O2="H","On Time",IF(O2="M",CHOOSE((N2=0)+1,"Late","Early")," "))

Which is a slightly modified version of yours, where N2 = 0 is Early as well, the adjustment is straightforward. Because it uses relative addresses (r1c1), you can populate the entire range with the function in one pass and it will update accordingly.

If lstRow < 3, simply populate P2. Hope this helps.

Regards,
Nate Oliver