COUNTIFS formula to refer to a dynamic range in another sheet from the active sheet

Question

I am trying to incorporate this COUNTIFS formula into a worksheet via VBA but cannot seem to get it to work and with my rather limited VBA skills I've hit a bit of a brick wall.

This is the formula I want to incorporate but I want the range to be dynamic rather than fixed:

=IF(COUNTIFS('Scheme Information'!$B$5:$B$20000,COMPILED!$A2,'Scheme Information'!$A$5:$A$20000,COMPILED!H$1)>0,"Yes","")   

The VBA code I have written always errors at the .Range stage.

Sub COUNTIFS_Formula()

    Dim SourceLastRow As Long
    Dim OutputLastRow As Long
    Dim sourceSheet As Worksheet
    Dim outputSheet As Worksheet

    'Name sheets for reference
    Set sourceSheet = Worksheets("Scheme Information")  ' SOURCE
    Set outputSheet = Worksheets("COMPILED")            ' OUTPUT

    'Determine last row of Scheme Information sheet
    With sourceSheet
        SourceLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With

    'Add in formula
    With outputSheet
        OutputLastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
        .Range("H2:H" & OutputLastRow).Formula = "=IF(COUNTIFS('" & sourceSheet.Name & "!$B$2:$B$" & SourceLastRow & "," _
            & outputSheet.Name & "$A2," & sourceSheet.Name & "'!$B$2:$B$" & SourceLastRow & "," & outputSheet.Name & "!H$1)>0" & "," & ""YES"" & "," & "")"
    End With

End Sub

Answer 1

It is much easier to solve a problem if you break it down into smaller parts.

.Range("H2:H" & OutputLastRow).Formula = "=IF(COUNTIFS('" & sourceSheet.Name & "!$B$2:$B$" & SourceLastRow & "," _ & outputSheet.Name & "$A2," & sourceSheet.Name & "'!$B$2:$B$" & SourceLastRow & "," & outputSheet.Name & "!H$1)>0" & "," & ""YES"" & "," & "")"

At the very least you should assign the formula to a string variable before you assign it to the Range.Formula. This makes it easy to print the result out to the Immediate Window for inspection.

Sub COUNTIFS_Formula()
    Dim Source As Range
    Dim SourceFormula As String
    With wsSchemeInformation
        Set Source = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)).Offset(0, 1)
        SourceFormula = FormulaAddress(Source)
    End With

    Dim OutputFormula As String, OutputA2Formula As String, OutputH2Formula As String
    Dim Output As Range
    With wsCompiled
        Set Output = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)).Offset(0, 1)
        
        OutputFormula = FormulaAddress(Output)
        OutputA2Formula = FormulaAddress(.Range("A2"))
        OutputH2Formula = FormulaAddress(.Range("H2"))
    End With
    
    Dim FormulaRange As Range
    Set FormulaRange = Output.EntireRow.Columns("H")
    
    Dim FormulaParts As Variant
    FormulaParts = Array("=IF(COUNTIFS(", SourceFormula, ",", OutputA2Formula, ",", SourceFormula, ",", OutputH2Formula, ")>0", ",", """YES""", ")")
    
    Dim Formula As String
    Formula = Join(FormulaParts, "")
    FormulaRange.Formula = Formula

End Sub

Function FormulaAddress(Target As Range)
    FormulaAddress = "'" & Target.Parent.Name & "'!" & Target.Address
End Function

Function wsCompiled() As Worksheet ' OUTPUT
    Set wsCompiled = ThisWorkbook.Worksheets("COMPILED")
End Function

Function wsSchemeInformation() As Worksheet ' SOURCE
    Set wsSchemeInformation = ThisWorkbook.Worksheets("Scheme Information")
End Function