excel - 想要从另一个工作簿中使用 SUMIF 带来匹配数据的值

Question

所以代码如下

Sub SumIF_test()

    thwb = "Macro Open Another Worksheet.xlsm"
    thws = "Sheet1"
    Workbooks(thwb).Worksheets(thws).Range("B5").Select
    ActiveCell.WorksheetFunction.SumIf(Workbooks("CreditAnalystofBank.xlsx").Worksheets("REBanco").Range("A:A"), Range("A7"), Workbooks("CreditAnalystofBank.xlsx").Worksheets("REBanco").Range("H:H")) = a MsgBox (a)
End Sub

给出像'expected ='这样的错误，这就是我添加变量a的原因，我需要将数据放在特定的单元格上。现在，它给了 Object 不支持这个属性或方法

标签： excelvbasumifs

Answer 1

VBA SumIf

• 您可以通过多种方式做到这一点。
• 第一个示例显示了如何通过将所有值放入常量来实现。在这个阶段，它可能缺乏可读性。
• 第二个示例显示了如何通过仅将工作簿和工作表名称放入常量和变量中来做到这一点。它变得更具可读性。
• 第一个示例还显示了如何使用Application.SumIfor WorksheetFunction.SumIf（不需要Applicationwith WorksheetFunction）。对于其他功能，以一种或另一种方式执行此操作可能会有所不同，我不会在这里说这种情况。
• s - 源 , d - 目的地

Option Explicit

Sub SumIfFull()

    ' Constants
    
    Const swbName As String = "CreditAnalystofBank.xlsx"
    Const swsName As String = "REBanco"
    Const srAddress As String = "A:A" ' Range
    Const ssrAddress As String = "H:H" ' Sum Range
    
    Const dwbName As String = "Macro Open Another Worksheet.xlsm"
    Const dwsName As String = "Sheet1"
    Const drAddress As String = "B5" ' Result
    Const dcAddress As String = "A7" ' Criteria
    
    ' Workbook, Worksheet and Range (Cell) References
    
    Dim swb As Workbook: Set swb = Workbooks(swbName)
    Dim sws As Worksheet: Set sws = swb.Worksheets(swsName)
    Dim srg As Range: Set srg = sws.Range(srAddress) ' Range
    Dim ssrg As Range: Set ssrg = sws.Range(ssrAddress) ' Sum Range
    
    Dim dwb As Workbook: Set dwb = Workbooks(dwbName)
    Dim dws As Worksheet: Set dws = dwb.Worksheets(dwsName)
    Dim dcCell As Range: Set dcCell = dws.Range(dcAddress) ' Criteria
    Dim drCell As Range: Set drCell = dws.Range(drAddress) ' Result
    
    ' Write
    
    ' Late-Bound
    drCell.Value = Application.SumIf(srg, dcCell.Value, ssrg)
    ' Early Bound
    'drCell.Value = WorksheetFunction.SumIf(srg, dcCell.Value, ssrg)

End Sub

Sub SumIfWbWs()

    ' Constants
    
    Const swbName As String = "CreditAnalystofBank.xlsx"
    Const swsName As String = "REBanco"
    
    Const dwbName As String = "Macro Open Another Worksheet.xlsm"
    Const dwsName As String = "Sheet1"
    
    ' Only Worksheet and Workbook References
    
    Dim swb As Workbook: Set swb = Workbooks(swbName)
    Dim sws As Worksheet: Set sws = swb.Worksheets(swsName)
    
    Dim dwb As Workbook: Set dwb = Workbooks(dwbName)
    Dim dws As Worksheet: Set dws = dwb.Worksheets(dwsName)
    
    ' Write
    
    ' Late-Bound
    dws.Range("B5").Value _
        = Application.SumIf(sws.Range("A:A"), dws.Range("A7"), sws.Range("H:H"))

End Sub