FormulaR1C1 not running across a selected range

Question

I'm trying to make a macro where I can find a set of products and get an average weekly sales figure. However, FormulaR1C1 is only applying to the first figure in a selection and not all active cells. Any help as to why it doesn't apply for the whole range?

Option Compare Text
Private Sub submit_Click()

Dim emptyRow As Long
Dim Rng As Range
Dim myCell As Object
Dim mySelection As Range

Range("R1:S1").Select
Range("R1:S1").Clear
ActiveCell.FormulaR1C1 = ""

emptyRow = WorksheetFunction.CountA(Range("R:R")) + 1

Cells(emptyRow, 18).Value = search.Value
Cells(emptyRow, 19).Value = week.Value

 Set Rng = Range("A1:A2000")
 searchString = Range("R1")
 For Each myCell In Rng
 If InStr(myCell.Text, searchString) Then
      If Not mySelection Is Nothing Then
          Set mySelection = Union(mySelection, myCell)
      Else
          Set mySelection = myCell
      End If
 End If
 Next
 If mySelection Is Nothing Then
     MsgBox "The product was not found in the selection"
 Else
     mySelection.Offset(0, 4).Select
 End If

    ActiveCell.FormulaR1C1 = "=RC[-2] /" & Range("S1")

Unload Me

End Sub