Run a SUMPRODUCT in VBA

[drchris]

H guys,
Can I get some help with the following:

As part of a Sub id like to run the following function and save it to a variable.

Please Login or Register  to view this content.

How can I do it?

I've tried this but I get a Type Mismatch error:

Please Login or Register  to view this content.

And also this:

Please Login or Register  to view this content.

Again same Type Mismatch error!

Can I get some help please!

Thanks
Chris

[DonkeyOte]

In your first attempt you have a superfluous closing parentheses which is the cause of the error.

Generally speaking replicating worksheet SUMPRODUCTs from VBA are conducted via Evaluate.
(whether you opt to use [ ] rather than Evaluate( ) is something of a personal choice though the two can generate different results)

[drchris]

Thanks DonkeyOte,

How do I go about doing it via Evaluate then please?

[DonkeyOte]

Your first example is using Evaluate just in "shorthand" form [ ]

The equivalent longer version would be:

Please Login or Register  to view this content.

note the double up of quotes... for this reason some prefer the [ ] method.

Evaluate will evaluate any unqualified ranges against the active sheet at run time unless otherwise stipulated.
It follows that if you want your SUMPRODUCT to refer to ranges on a sheet other than the active sheet at run time you should qualify the appropriate sheet either by

a) stipulating the relevant sheet in the SUMPRODUCT itself

Please Login or Register  to view this content.

or

b) qualifying the Evaluate call

Please Login or Register  to view this content.

As mentioned the [ ] and Evaluate( ) methods can generate different outputs (though not necessarily relevant here), example:

Please Login or Register  to view this content.

v1 generates a string whereas v2 generates a 2 dimensional variant array

[drchris]

thank you mate - makes absolute sense. its no wonder you are a guru!
ive just tried both [] and evaluate and it gives me the same results so ill stick to [] cause its a little bit cleaner. in any case though your explanation was spot on.
cheers
chris