R1C1 and named ranges

[288enzo]

After quite a bit of trial and error, I've come to realize when using R1C1 it isn't as simple as I had thought.

Fluff was kind of enough to show me the following:

        Set rng1 = Range(Cells(5, 10), Cells(LR, 10))
        rng1.FormulaR1C1 = "=RC25"

What I'm working on now is how to incorporate worksheet names.

This works fine

    Dim rng1, rng2 As Range
    Dim mySheet1 As Variant

    mySheet1 = "'Continuous Learning Details'!"
    
    With Sheets("Continuous Learning Details")
        LR = .Cells(.Rows.Count, 1).End(xlUp).Row
        Set rng1 = .Range(.Cells(4, 10), .Cells(LR, 10)) 'Email address
        Set rng2 = .Range(.Cells(4, 12), .Cells(LR, 12)) 'Package
    End With

    Cells(5, 14).Formula = "=COUNTIFS(" & mySheet1 & rng1.Address & ",L5," & mySheet1 & rng2.Address & ",""Q1*"")"

What do I need to do to make the last line work using R1C1?

    Cells(5, 14).FormulaR1C1 = "=COUNTIFS(" & mySheet1 & rng1.Address & ",RC[-2]," & mySheet1 & rng2.Address & ",""Q1*"")"

 

[288enzo]

I think I figured it out -

    Dim mySheet1, rng1, rng2 As Variant

    mySheet1 = "'Continuous Learning Details'!"
    
    With Sheets("Continuous Learning Details")
        LR = .Cells(.Rows.Count, 1).End(xlUp).Row
        rng1 = "R[-1]C[-4]:R[" & LR & "]C[-4]" 'Emil address
        rng2 = "R[-1]C[-2]:R[" & LR & "]C[-2]" 'Package
    End With

    Cells(5, 14).FormulaR1C1 = "=COUNTIFS(" & mySheet1 & rng1 & ",RC[-2]," & mySheet1 & rng2 & ",""Q1*"")"