vba - 打开多个工作簿和 SUM COUNTIFS

Question

我正在尝试打开多个工作簿，多个书籍/工作表中所有 COUNTIFS 的 SUM 值，并将该总和除以主工作簿中的现有值(不断打开)，并将最终值发送到主工作簿中的目标单元格工作簿。

Sub TotalCountIfs()

    Workbooks.Open "C:\Book1.xls"
    Workbooks.Open "C:\Book2.xls"
    Workbooks.Open "C:\Book3.xls"

    Workbooks.Master.Sheet1.Range(“B2″).Value = Application.WorksheetFunction.
    SUM
    (Workbooks.Book1.Sheet1.Countif(Range(“A2:A8″), “>”50,
    Workbooks.Book2.Sheet1.Countif(Range(“A2:A8″), “>”50,
    Workbooks.Book3.Sheet1.Countif(Range(“A2:A8″), “>”50)
    /Workbooks.Master.Sheet1.Range(“A2”).Value

    Workbooks("Book1.xls").Close
    Workbooks("Book2.xls").Close
    Workbooks("Book3.xls").Close

End Sub

为草率的代码道歉。只是试图传达所需的逻辑。我不是专家，但我认为函数或循环会为我省去一些麻烦，因为实际任务涉及打开 10 多个工作簿，每个工作簿具有 25 多个列范围。

Answer 1

试试这个方法。

Sub TotalCountIfs()

    Dim wbm As Workbook, wb1 As Workbook, wb2 As Workbook, wb3 As Workbook

    Set wbm = ThisWorkbook  '<~~ might need to clarify this
    Set wb1 = Workbooks.Open("C:\Book1.xls", ReadOnly:=True)
    Set wb2 = Workbooks.Open("C:\Book2.xls", ReadOnly:=True)
    Set wb3 = Workbooks.Open("C:\Book3.xls", ReadOnly:=True)

    wbm.worksheets(1).Range("B2").Value = _
       (application.countif(wb1.worksheets(1).Range("A2:A8"), ">50") + _
        application.countif(wb2.worksheets(1).Range("A2:A8"), ">50") + _
        application.countif(wb3.worksheets(1).Range("A2:A8"), ">50")) / _
        wbm.worksheets(1).Range("A2").Value

    wb1.Close savechanges:=False
    wb2.Close savechanges:=False
    wb3.Close savechanges:=False

End Sub

这假设 wbm.worksheets(1).Range(“A2”).Value 不为零或 #DIV/0!将导致错误。

小心那些“智能引号”它们在代码编辑器中是无效的。