Range producing runtime error 1004 when changed to cells

Question

The part below started to produce runtime error 1004 when I changed the range from letters to cells:

With letters:

Application.SumIf(Sheets(4).Range("E:P"), _
Sheets(6).Cells(i, 1).Value2, _
Sheets(4).Range("K:K"))

Without letters:

Application.SumIf( _ 
    Sheets(4).Range(Cells(1, 5), Cells(intLstRowA, 16)), _
    Sheets(6).Cells(i, 1).Value2, _
    Sheets(4).Range(Cells(1, 11), Cells(intLstRowA, 11)))

It is part of a larger loop:

intLstRowA = Sheets(4).Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To 9
    Sheets(6).Cells(i, 2) = _
        Format( _
            Application.SumIf( _
                Sheets(4).Range(Cells(1, 5), Cells(intLstRowA, 16)), _
                Sheets(6).Cells(i, 1).Value2, _
                Sheets(4).Range(Cells(1, 11), Cells(intLstRowA, 11))) _ 
                / _
            Application.SumIf(Sheets(4).Range("E:P"), _
                Sheets(6).Cells(i, 1).Value2, _
                Sheets(4).Range("P:P")) * 0.01, _
           "Percent")
Next i

I never use .Select, .Active etc. and I cannot figure out what is causing the error. Can you help me sanity check this?

Answer 1

It's probably because you aren't properly qualifying your Cells property calls. If you don't qualify, it will assume you're referring to the currently active sheet.

Application.SumIf( _ 
    Sheets(4).Range(Cells(1, 5), Cells(intLstRowA, 16)), _
    Sheets(6).Cells(i, 1).Value2, _
    Sheets(4).Range(Cells(1, 11), Cells(intLstRowA, 11)))
'                   ^^^^^         ^^^^^

On what sheet are these cells? Say so explicitly:

'                   vvvvvvvvvvvvvvv        vvvvvvvvvvvvvvv
Application.SumIf( _ 
    Sheets(4).Range(Sheets(4).Cells(1, 5), Sheets(4).Cells(intLstRowA, 16)), _
    Sheets(6).Cells(i, 1).Value2, _
    Sheets(4).Range(Sheets(4).Cells(1, 11), Sheets(4).Cells(intLstRowA, 11)))
'                   ^^^^^^^^^^^^^^^         ^^^^^^^^^^^^^^^

The former case will give you an error if a different sheet than Sheet(4) happens to be active, because it would be looking to define a range in one sheet whose corners are defined by cells on another sheet — clearly nonsensical.