VBA COUNTIFs formula to refer to a dynamic range in another sheet

Question

I am trying to incorporate a COUNTIFS formula into a worksheet via VBA - the formula I have works fine but it is not dynamic and with my rather limited VBA skills I've hit a bit of a brick wall.

To explain, the COUNTIFS look at 19 different headings (from H1 through to AA1) in the COMPILED worksheet, and count the occurence of "Yes"'s within a range in the TPR worksheet - it is this range that needs to be dynamic.

This is the VBA formula I am currently using, which works fine but, as mentioned, is a fixed range:

'Add in COUNTIFS formulas
     With Sheets("COMPILED")
        .Range("H2:AA" & .Cells(.Rows.Count, "A").End(xlUp).Row).FormulaR1C1 = "=IF(COUNTIFS(TPR!R5C2:R20000C2,COMPILED!RC1,TPR!R5C1:R20000C1,COMPILED!R1C)>0,""Yes"","""")"
     End With

Many thanks and kindest regards, TE

Answer 1

VBA Write COUNTIFS Formula

Option Explicit

Sub WriteCOUNTIFS()
    
    ' =IF(COUNTIFS(TPR!$B$5:$B$10,Compiled!$A2,
    '  TPR!$A$5:$A$10,Compiled!H$1)>0,"Yes","")
    
    Const sName As String = "TPR"
    Const shCol As String = "A"
    Const svCol As String = "B"
    Const sfRow As Long = 5
    
    Const dName As String = "COMPILED"
    Const dlrCol As String = "A"
    Const dCols As String = "H:AA"
    Const dfRow As Long = 2
    
    Dim sws As Worksheet: Set sws = ThisWorkbook.Worksheets(sName)
    Dim slRow As Long: slRow = sws.Cells(sws.Rows.Count, "A").End(xlUp).Row
    Dim srCount As Long: srCount = slRow - sfRow + 1
    Dim shcrg As Range
    Set shcrg = sws.Cells(sfRow, shCol).Resize(srCount)
    Dim shAddress As String: shAddress = "'" & sName & "'!" & shcrg.Address
    Dim svcrg As Range: Set svcrg = shcrg.EntireRow.Columns(svCol)
    Dim svAddress As String: svAddress = "'" & sName & "'!" & svcrg.Address
    
    
    Dim dws As Worksheet: Set dws = ThisWorkbook.Worksheets(dName)
    Dim dhrrg As Range: Set dhrrg = dws.Columns(dCols).Rows(1)
    Dim dlRow As Long: dlRow = dws.Cells(dws.Rows.Count, dlrCol).End(xlUp).Row
    Dim drCount As Long: drCount = dlRow - dfRow + 1
    Dim drg As Range
    Set drg = dws.Columns(dCols).Resize(drCount).Offset(dfRow - 1)
    Dim dhAddress As String: dhAddress = dhrrg.Cells(1).Address(, 0)
    Dim dvAddress As String: dvAddress = dws.Cells(dfRow, dlrCol).Address(0)
    
    drg.Formula = "=IF(COUNTIFS(" & svAddress _
        & "," & dvAddress & "," & shAddress & "," & dhAddress _
        & ")>0,""Yes"","""")"

    'Debug.Print svAddress, dvAddress
    'Debug.Print shAddress, dhAddress

End Sub