COUNTIFS 公式，用于引用活动工作表中另一张纸中的动态范围-解网

问：

我正在尝试通过 VBA 将此 COUNTIFS 公式合并到工作表中，但似乎无法让它工作，并且由于我相当有限的 VBA 技能，我遇到了一些砖墙。

这是我想合并的公式，但我希望范围是动态的而不是固定的：

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

我编写的 VBA 代码总是在 .范围阶段。

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

VBA Excel-365

非常感谢 - 我已经进行了您建议的更改，我不再收到错误，但是，公式返回为 #VALUE！s - 这是结果公式： =IF（COUNTIFS（'方案信息！$B$2：[$B$17718，编译！$A 2，方案信息]$B$17718，编译！$A 2，方案输入！$B$2：$B$17718，编译！H$1）>0，“是”，“”）

答：

0赞 TinMan 3/11/2022 #1

如果将问题分解成更小的部分，则解决问题要容易得多。

.范围（“H2：H” & OutputLastRow）。公式 = “=IF（COUNTIFS（'” & sourceSheet.Name & “！$B$2：$B$” & SourceLastRow & “，” _ &outputSheet.Name & “$A 2，” & sourceSheet.Name & “'！$B$2：$B$” & SourceLastRow & “，” & outputSheet.Name & “！H$1）>0“ & ”，“ & ”是“” & “，” & “”）”

至少，您应该先将公式分配给字符串变量，然后再将其分配给 .这样可以很容易地将结果打印到即时窗口进行检查。Range.Formula

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

谢谢TinMan - 这太棒了。我确实考虑过将其分解成更小的部分，但由于 COUNTIFS 需要检查从 H1 到 Z1 的 19 个不同的标题，我认为我可以简单地重复该公式，相应地每次都以增量方式调整代码。是否有可能调整您的代码来执行此操作？我很感激我原来的帖子中没有提到这一点，但我打算让那个公式起作用，然后将其应用于其他 19 个标题。

0赞 TinMan 3/12/2022

@TheEndUK II 对问题还不够了解，无法说出什么是最好的方法。这就像调整范围锚点（$，相对和绝对参考）一样简单。

0赞 TheEndUK 3/14/2022

谢谢 TinMan - 这是我一直在使用的 VBA，但我想让它在 TPR 工作表中引用的范围动态化：With Sheets（“COMPILED”）。范围（“H2：AA” & .单元格（.Rows.Count，“A”）。结束（xlUp）。行）。公式R1C1 = “=IF（COUNTIFS（TPR！R5C2：R20000C2，编译！RC1，TPR！R5C1：R20000C1，编译！R1C）>0，“”是“，”“）”结尾

0赞 TinMan 3/15/2022

@TheEndUK 您应该将其作为不同的问题发布。很难在评论中回答。提供模拟工作簿下载链接也会有所帮助。阅读 Excel VBA 中的 R1C1 样式表示法和 FormulaR1C1 属性：教程和示例。动态行：

1赞 TheEndUK 3/16/2022

感谢您的帮助和建议，TinMan - 我会按照建议去做并开始一个新帖子。此致，TE

上一个：计算同一行中 3 列的匹配项数

下一个：VBA VLOOKUP 公式，用于引用另一张工作表中的动态范围

COUNTIFS 公式，用于引用活动工作表中另一张纸中的动态范围

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

评论

评论