How can I make sure that my code runs properly all the time

Question

I have this code running smoothly when I step through the code (F8), but when I run it with F5 or call it to run from a button it doesn't do what it's supposed to. It only does the lookup in the first cell (Q2) and leaves the rest blank - like it skipped to run the formula down to the last row.

How can I improve my code to make sure that it always runs as it should?

Sub LookupFilename()
' Looks up the filename to be set according to Team Name

Application.ScreenUpdating = False

LastRow = Cells(Rows.Count, "A").End(xlUp).Row

    Range("Q2").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(VLOOKUP(RC[-3],Controller!C9:C12,4,FALSE),""Other"")"
    Range("Q2").AutoFill Destination:=Range("Q2:Q" & LastRow)

Application.ScreenUpdating = True

MsgBox "Successful data collection.", vbInformation, "Success"

End Sub

Answer 1

There is no need to Select or use ActiveCell or AutoFill. Replace:

Range("Q2").Select
ActiveCell.FormulaR1C1 = _
    "=IFERROR(VLOOKUP(RC[-3],Controller!C9:C12,4,FALSE),""Other"")"
Range("Q2").AutoFill Destination:=Range("Q2:Q" & LastRow)

with:

Range("Q2:Q" & LastRow).FormulaR1C1 = _
    "=IFERROR(VLOOKUP(RC[-3],Controller!C9:C12,4,FALSE),""Other"")"

Note, you shouldn't be Activateing either. Instead, qualify your Range, Cells, and Rows calls with the appropriate worksheet. Note the . before Cells, Rows and Range below:

Dim Data As Worksheet
Set Data = ThisWorkbook.Worksheets("Data")

With Data
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    .Range("Q2:Q" & LastRow).FormulaR1C1 = _
        "=IFERROR(VLOOKUP(RC[-3],Controller!C9:C12,4,FALSE),""Other"")"
End With