For each cl ... case select working as group

[Matt111]

Hi all,
The following code is supposed to run through whatever cells the user has currently selected with their mouse, and apply a formula which varies according to whats in the col header is in row 2. What i dont get is why when it runs, instead of evaluating each cell individually, and applying the appropriate formula (depending on the case select) it seems to do the Case Select on the first cell, and apply the same formula across all cells selected. I want it to assess one cell, apply appropriate formula, move on to the next, case select and apply formula, etc. Can anyone point me in the right direction please?

Sub AddFormula()
    
    Dim cl As Range
    Dim ColHeading As String

    For Each cl In Selection
   
        ColHeading = Cells(2, cl.Column).Value   
        Select Case heading
               
            Case "SalesQty"
            Selection.FormulaR1C1 = "=SUMPRODUCT((R1C=SalesData!R2C1:R140193C1)*(Main!RC12=SalesData!R2C4:R140193C4)*(Main!RC11=SalesData!R2C2:R140193C2)*(SalesData!R2C5:R140193C5))"

            Case "SalesRev"
            Selection.FormulaR1C1 = "=SUMPRODUCT((R1C=SalesData!R2C1:R140193C1)*(Main!RC12=SalesData!R2C4:R140193C4)*(Main!RC11=SalesData!R2C2:R140193C2)*(SalesData!R2C6:R140193C6))"

            Case "SalesCost"
            Selection.FormulaR1C1 = "=SUMPRODUCT((R1C=SalesData!R2C1:R140193C1)*(Main!RC12=SalesData!R2C4:R140193C4)*(Main!RC11=SalesData!R2C2:R140193C2)*(SalesData!R2C7:R140193C7))"

            Case "GratisCost"
            Selection.FormulaR1C1 = "=SUMPRODUCT((R1C=SalesData!R2C1:R140193C1)*(Main!RC12=SalesData!R2C4:R140193C4)*(Main!RC11=SalesData!R2C2:R140193C2)*(SalesData!R2C11:R140193C11))"

            Case "Net Margin"
            Selection.FormulaR1C1 = "=RC[-3]-RC[-2]-RC[-1]"
    
            End Select
        
        Next cl
    
    'Selection.Copy
    'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

End Sub

 

[VoG]

Shouldn't it be like this?

Select Case ColHeading
               
            Case "SalesQty"
            cl.FormulaR1C1 = "=SUMPRODUCT((R1C=SalesData!R2C1:R140193C1)*(Main!RC12=SalesData!R2C4:R140193C4)*(Main!RC11=SalesData!R2C2:R140193C2)*(SalesData!R2C5:R140193C5))"

 

[Matt111]

Ah blimey it's CL not SELECTION! Yes - thank you!
(The change in name to ColHeading is a bit of a side, i changed it a bit when posting thinking it would help the reader). Thats sorted now thank you very much, Cheers. Matt.