VBA Excel - Run-time Error 13 Type Mismatch when using WorksheetFunction.SumIF

Question

I have two Sheets of data. One Sheets("Expenses") is populated with all of my bills and what account they are associated to in the Rows and all of the Columns are a consecutive list of dates. The cells beneath the dates are only filled if the Expense for that particular Row is due on that Date.

In the second Sheets("Totals"), I have each Account listed in the Rows and the Dates consecutively listed in the Columns much like the Sheets("Expenses"). What I want to do is to use WorksheetFunction.SumIf is to populate the cells on `Sheets("Totals") with the total amounts of each Account on each Date.

There is a little bit of redundant code in what I have below but that's just because I haven't got this working yet or optimised.

The Problem that I am getting is a Run-Time Error 13: Type Mismatch on the line accSum = WorksheetFunction.SumIf(objSum, objExpAcc, strAccount). I thought that I followed the MSDN examples properly but for some reason I can't get it to work.

I don't know if it makes any difference but on Sheets("Expenses") the first two Rows are header rows that contain a mixture of Dates and Strings.

Public Sub upAccRows()
Dim startDate As Date
Dim endDate As Date
Dim dExp As Integer
Dim stDateCol As Integer
Dim lDateCol As Integer
Dim lExp As Integer
Dim strAccount As String
Dim lRow As Integer
Dim currentDate As Date
Dim objSum As Range
Dim objExpAcc As Range
Dim accSum As Integer
Dim strTest As String

startDate = Worksheets("Summary").Range("B2").Value
endDate = Worksheets("Summary").Range("B1").Value
lRow = Worksheets("Expenses").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row 'Last row of entries

With Sheets("Totals")
    startExp = .Cells.Find("Expenses", SearchOrder:=xlByRows, SearchDirection:=xlNext).Row + 1
    startInc = .Cells.Find("Income", SearchOrder:=xlByRows, SearchDirection:=xlNext).Row
    stDateCol = .Cells.Find(What:=startDate, SearchOrder:=xlByColumns, SearchDirection:=xlNext).Column
    lDateCol = .Cells.Find(What:=endDate, SearchOrder:=xlByColumns, SearchDirection:=xlNext).Column
    lExp = startInc - 1
End With
'~~> Loops through the rows of the Accounts on the Accounts Sheet
For CountA = startExp To lExp
    '~~> Loops through the columns of the Dates on the Accounts Sheet
    For CountB = stDateCol To lDateCol
        '~~> Set Variables for the SumIf Function and Do
        With Sheets("Totals")
            currentDate = .Cells(1, CountB).Value
            strAccount = .Cells(CountA, 2).Value
        End With
        With Sheets("Expenses")
            dExp = .Cells.Find(What:=currentDate, SearchOrder:=xlByColumns, SearchDirection:=xlNext).Column
            Set objSum = .Columns(dExp)
            Set objExpAcc = .Columns(3)
            accSum = WorksheetFunction.SumIf(objSum, objExpAcc, strAccount)
            MsgBox "accSum = " & accSum
        End With
        'Sheets("Totals").Cells(CountA, CountB).Value = accSum
        dExp = dExp + 1
    Next CountB
Next CountA

End Sub

Answer 1

The order of your parameters to SumIf is wrong.

Function parameters are:

SUMIF(range, criteria, [sum_range])

Based on your variable names, try

accSum = WorksheetFunction.SumIf(objExpAcc, strAccount, objSum)