SUMPRODUCT limit

[tiredofit]

Is there a limit to the SUMPRODUCT?

This fails:

Dim LastRow as Integer

LastRow = 1000

Dim a As Variant

With Sheet1

a = Evaluate("=SUMPRODUCT((-1*('" & .Name & "'!AN2:AN" & LastRow & "+'" & .Name & "'!AR2:AR" & LastRow & ")/'" & .Name & "'!H2:H" & LastRow & "/12<=" & wksResults.Range("Monthly").Value & ")*('" & .Name & "'!H2:H" & LastRow & ">" & wksResults.Range("Monthly").Value & ") * ('" & .Name & "'!H2:H" & LastRow & "<=" & wksResults.Range("Monthly").Value & ") *( -1*('" & .Name & "'!AN2:AN" & LastRow & "+'" & .Name & "'!AR2:AR" & LastRow & ")/'" & .Name & "'!H2:H" & LastRow & "/12))")

End With

whereas this works:

Dim LastRow as Integer

LastRow = 1000

Dim a As Variant

With Sheet1

a = Evaluate("=SUMPRODUCT((-1*(AN2:AN" & LastRow & "+AR2:AR" & LastRow & ")/'" & .Name & "'!H2:H" & LastRow & "/12<=" & wksResults.Range("Monthly").Value & ")*('" & .Name & "'!H2:H" & LastRow & ">" & wksResults.Range("Monthly").Value & ") * ('" & .Name & "'!H2:H" & LastRow & "<=" & wksResults.Range("Monthly").Value & ") *( -1*('" & .Name & "'!AN2:AN" & LastRow & "+'" & .Name & "'!AR2:AR" & LastRow & ")/'" & .Name & "'!H2:H" & LastRow & "/12))")

End With

The difference being the second one has fewer of these:

'" & .Name & "'!

Thanks

 

[Dryver14]

Not my forte

this looks wrong "+'" but I could be wrong

 

[tiredofit]

No, I can assure you that's not the problem.

 

[RoryA]

There is a 255 character limit to the formula string you can pass to Evaluate. Perhaps you're hitting that. You'd be better off using Sheet1.Evaluate as it will evaluate the formula in the context of that sheet so you don't have to qualify all those addresses.

 

[tiredofit]

Thanks.

My alternative was to write Sheet1.Select or Sheet1.Activate but I'd rather not do that.

 

[jproffer]

Not to answer for Rory, but you don't have to activate it...you just have to specify it, like he said. Sheet1.Evaluate

 

[FormR]

using Sheet1.Evaluate as it will evaluate the formula in the context of that sheet

That's cool, I didn't know you could do that

 

[RoryA]

Yup - and it's generally faster than Application.Evaluate too.