sumif issues

[dgrimm]

I am trying to sum according to year and I am getting an application or object defined error.

Here is the piece of code that is getting the error.

Sheets(wsr).Select
Set speccol = Range("d9:d" & wsrcnt)
Set aggcol = Range("e9:e" & wsrcnt)
Set specpd = Range("j9:j" & wsrcnt)
Set aggpd = Range("k9:k" & wsrcnt)
Set specult = Range("l9:l" & wsrcnt)
Set aggult = Range("k9:k" & wsrcnt)

For i = 0 To difyr
Sheets(wsr).Range("c" & newwsrcnt + 2 + i).Formula = "=SumIf((""b9:b"" & wsrcnt), minyr + i, speccol)"
Next i

The line after For i = 0 to difyr.

I don't know if it is my "" in the formula or what. I have speccol dimmed as a range (which has a lot of blanks in it). If you need more info please ask away. All the other variables I have have been using before, the only new one is the speccol variable. So I am believing it is with that or just my equation in general.

Any help would be greatly appreciated.



Thanks in advance

Dave

 

[Joe4]

The range quotes can be confusing when you already have quotes to indicate your formula. It might be a little easier to break it up and build the range outside of the formula in a variable and reference it.

Here is what that loop might look like:

    Dim myRange as String
    For i = 0 To difyr
        myRange = "B9:B" & wsrcnt
        Sheets(wsr).Range("c" & newwsrcnt + 2 + i).Formula = "=SumIf(" & myRange & "," & minyr + i & "," & speccol.Address & ")"
    Next i