Sum Product Type mismatch error

[Billy C]

Can anyone tell me why I am getting a Type mismatch error please

Sub SumProduct()
Dim WF As WorksheetFunction
Set WF = Application.WorksheetFunction
Dim Product As Range, Region As Range, Rep As Range, Cust As Range, Sales As Range, COGS As Range
Set Product = Range("B2:B106") 'contains text
Set Region = Range("C2:C106") 'contains text
Set Rep = Range("D2:D106") 'contains text
Set Cust = Range("E2:E106") 'contains text
Set Sales = Range("G2:G106") 'contains values
Set COGS = Range("H2:H106") 'contains values

ActiveCell.Formula = WF.SumProduct((Region = East) * Sales)

End Sub

 

[Jonmo1]

That line isn't really putting a formula in the cell, it's trying to use VBA's version of sumproduct to put it's result in the cell.
The VBA version of Sumproduct can't do the criteria type of evaluations like we use it for in formulas.

Try
ActiveCell.formula = "=SUMPRODUCT((" &Region.Address & "=""East"")*" & Sales.Address & ")"

or
ActiveCell.formula = "=SUMIF(" & Region.Address & ",""East""," & Sales.Address & ")"

 

[Billy C]

Sorry to be a nuisance, your code worked when I was doing a sumif from the same workbook but when I tried to update your code to account for between two workbooks I came unstuck
DataFile is the workbook containing the Range and Sum Range, MnRegNo is within the main workbook which holds the Criteria

Can you help?

ActiveCell.Formula = "=SUMIF(" & [DataFile] & ShName & DtaRegNo.Address & "," & MnRegNo.Address(0, 0) & "," & [DataFile] & ShName & SrVals.Address & ")"