sumif across sheets in VBA

[munkle6]

Hi there, I'm fairly new to Excel VBA. I'm trying to calculate sumifs across sheets. The following code results in an application defined error:

Sub fpsavings2()
Range("C4").Select
ActiveCell.Value=Application.SumIf(Sheets("output").Range(Cells(4, 15),Cells(33, 15)), "", Sheets("Optimisationplan").Range(Cells(1, 1), Cells(33, 1)))


End Sub

Any help much appreciated, thanks!

 

[Bong]

I think Application.SumIf(Sheets("output").Range(...
should be: Application.[red]worksheetfunction[/red].SumIf(Sheets("output").Range(...

_________________
Bob Rashkin

 

[munkle6]

Thanks, but that doesn't seem to make any difference- same error message. Do you (or anyone else) know an easy way to sumif across sheets like this using vba?

 

[Bong]

Well, if I'm going to use VBA anyway, I'd just do it in code:

a=0
for i=1 to rangeLimit
   if <condition is met> then a = a + <value from cell>
next

_________________
Bob Rashkin

 

[munkle6]

Thanks for that- will give it a try!

 

[firefytr]

Your ranges need to be the same size. You might want to try a syntax such as ..

    Dim wsO As Worksheet, wsOP As Worksheet
    Set wsO = Sheets("output")
    Set wsOP = Sheets("Optimisationplan")
    Range("C4").Value = WorksheetFunction.SumIf(wsO.Range(wsO.Cells(4, 15), wsO.Cells(33, 15)), "", _
                                                wsOP.Range(wsOP.Cells(1, 1), wsOP.Cells(33, 1)))

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[munkle6]

Thanks- that does exactly what I needed.