CODEWITHSUNDEEP
excelvbasumifs
xlmaster
xlmaster

Reputation: 731

Want to bring values of matched data with SUMIF from another workbook

So code is below

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

Gives error like 'expected =' that's why I added variable a, I need to land the data on specific cell. Now, it gives Object doesnot support this property or method

Upvotes: 1

Views: 247

Answers (1)

VBasic2008
VBasic2008

Reputation: 55073

VBA SumIf

  • You can do this in a number of ways.
  • The first example shows how to do it by putting all values into constants. At this stage, it might lack readability.
  • The second example shows how to do it by putting only the workbook and worksheet names into constants and variables. It becomes somewhat more readable.
  • The first example also shows how you can use Application.SumIf or WorksheetFunction.SumIf (no need for Application with WorksheetFunction). There may be differences in doing it one way or the other for other functions, which I wouldn't say is the case here.
  • s - Source , d - Destination
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

Upvotes: 1

Related Questions