Inserting Formula
I'll take a peek at it in the morning, Nate. Appreciate very much the help. Think I might need to do a little reading on macros....
"Nate Oliver" wrote:
Jeff, try the following:
Sub tester()
Dim lstRow As Long
Let lstRow = Range("a65536").End(xlUp).Row
Application.ScreenUpdating = False
Range(Cells(2, 1), Cells(lstRow, 15)).Sort _
Key1:=Range("O2"), Order1:=xlDescending, Header:=xlGuess
Columns("O:O").TextToColumns Destination:=Range("O1"), _
DataType:=xlFixedWidth, OtherChar:="|", _
FieldInfo:=Array(Array(0, 2), Array(7, 1))
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
Application.ScreenUpdating = True
End Sub
You only want one subroutine. So, one Sub ___() and one End Sub. Also, there's no need to select ranges and declare your variables at the top of the procedure, it makes your code easier to follow/maintain.
Regards,
Nate Oliver
|