FormulaR1C1 not running across a selected range-CodePudding

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

CodePudding user response：

The selection may contain more than one cell, but only one is the active cell.

    If mySelection Is Nothing Then
         MsgBox "The product was not found in the selection"
    Else
         mySelection.Offset(, 4).FormulaR1C1 = "=RC[-2] /" & Range("S1")
    End If