Sheet name not showing in formula

[288enzo]

Good morning,

Will someone please explain why my formula isn't including the name of the sheet?

    Dim CRange As Variant
    Set CRange = Sheets("KPI Data - MY22 ONLY").Range("C5:C" & Sheets("KPI Data - MY22 ONLY").Range("A" & Rows.Count).End(xlUp).Row)
    
    Range("G97") = "=countifs(" & CRange.Address & ",C97," & CRange.Offset(0, 19).Address & ",""Yes"")"

The output is - =COUNTIFS($C$5:$C$3407,C97,$V$5:$V$3407,"Yes")

I'm trying to achieve - =COUNTIFS('KPI Data - MY22 ONLY'!$C$5:$C$3407,C97,'KPI Data - MY22 ONLY'!$V$5:$V$3407,"Yes")

Thank you

 

[288enzo]

I came up with the below, but I'm sure there is a better way?

    Range("G97") = "=countifs('KPI Data - MY22 ONLY'!" & CRange.Address & ",C97,'KPI Data - MY22 ONLY'!" & CRange.Offset(0, 19).Address & ",""Yes"")"

 

[Fluff]

Try it like

    Range("G97") = "=countifs(" & CRange.Address(external:=True) & ",C97," & CRange.Offset(0, 19).Address(external:=True) & ",""Yes"")"

 

[288enzo]

Try it like

    Range("G97") = "=countifs(" & CRange.Address(external:=True) & ",C97," & CRange.Offset(0, 19).Address(external:=True) & ",""Yes"")"

Something new learned again, thank you as always Fluff.

(external:=True)

 

[Fluff]

You're welcome & thanks for the feedback.