I think I've almost got it working like I want.
I put this code in a module:
Function LastCell(ws As Worksheet) As Range
Dim LastRow&, LastCol%
On Error Resume Next
LastRow& = .Cells.Find(What:="*", SearchDirection:=xlPrevious, _
LastCol% = .Cells.Find(What:="*", _
Set LastCell = ws.Cells(LastRow&, LastCol%)
Then I call that function from the macro I created, like so:
Dim sLast As String
sLast = LastCell(Sheet1).Row
ActiveCell.FormulaR1C1 = "=IF(RC[-16]=""0247"",""W"",LEFT(RC[ 18],1))"
Selection.AutoFill Destination:=Range("X2:X" + sLast), Type:=xlFillDefault
Range("X2:X" + sLast).Select
ActiveWindow.ScrollRow = 2
The only thing now is that sLast is getting a value of 11 every time and I haven't been able to figure out why yet. There are about 8,000 rows of data, so 11 is a tad off.