On 02/07/2012 14:37, Vacuum Sealed wrote:
Sub Update_Col_C()

Dim sSht As Worksheet
Dim mysYear As Range, mysMonth As Range, mysEmp As Range, mysExp As
Range, mysAmt As Range

Dim tSht As Worksheet
Dim mytYear As Range, mytMonth As Range, mytEmp As Range, mytExp As Range
Dim c1 As Range, cCell As Range

Set sSht = Worksheets("Expenses")
Set mysYear = sSht.Range("$A$2:$A$5000")
Set mysMonth = sSht.Range("$B$2:$B$5000")
Set mysEmp = sSht.Range("$D$2:$D$5000")
Set mysExp = sSht.Range("$E$2:$E$5000")
Set mysAmt = sSht.Range("$H$2:$H$5000")

Set tSht = Worksheets("Summary")
Set mytYear = tSht.Range("$B$1")
Set mytMonth = tSht.Range("$D$1")
Set mytEmp = tSht.Range("$C$2")


''used syntax that failed envoking ( Type MisMatch Error 13 ):
''Set mytexp = ccell.offset(0, -2).value


Set c1 = tSht.Range("C3:C134")

For Each cCell In c1
With cCell
.Value = WorksheetFunction.SumProduct((mysYear = mytYear) *
(mysMonth = mytMonth) * (mysEmp = mytEmp) * (mysExp = mytExp) * (mysAmt))
End With
Next cCell

End Sub

It won't like this mess at all.
I think you are confused and I cannot guess what your intentions are.

The syntax of sumproduct is (range, range, range)
with at least two identical ranges separted by commas.

You possibly want to use formula array to do this computation eg.

Range("I9").FormulaArray = "=SUM(R[-4]C[-3]:RC[-3],R[-4]C[-2]:RC[-2])"

You might be able to capture it with the macro recorder on a good day
with the wind blowing in the right direction.

--
Regards,
Martin Brown