Run-Time Error '13': Type mismatch, in the following code:

Question

Sub test()

    'Dim total(1 To 9) As Variant
    ReDim total(1 To 9) As Variant


    For i = 1 To 9

    total(i) = Application.WorksheetFunction.SumIf(Sheets("Sheet2").Range(Cells(3, 3), Cells(i, 3)), ">" & _
    Sheets("Sheet2").Range(Cells(3, 6), Cells(i, 6))-60, _
    Sheets("Sheet2").Range(Cells(3, 4), Cells(i, 4)))
    Next i


End Sub

Can anyone tell why i keep getting this error although i am using variant for my array? everything seems fine to me. What i am trying to do is to deduct 60 days from the date in the cells, then sum up the amount between the initial date and deducted date. so it would be like: enter image description here

Answer 1

1. Sheets("Sheet2").Range(Cells(3, 6), Cells(i, 6)) (the criteria parameter) is expected to be a single cell or value, not a range.

2. The Cells(3, 3), Cells(i, 3) that define Sheets("Sheet2").Range(...) have no defined parent worksheet. See Is the . in .Range necessary when defined by .Cells?

   with workSheets("Sheet2")
       For i = lbound(total) To ubound(total)
           total(i) = Application.SumIf(.Range(.Cells(3, 3), .Cells(i, 3)), _
                                        ">" & .Cells(i, 6), _
                                        .Range(.Cells(3, 4), .Cells(i, 4)))
       Next i
   end with