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
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.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