VBA Sumproduct Coding

[mowens74]

I am having one issue after another trying to sort out a VBA 'Sumproduct' piece of code.

Please Login or Register  to view this content.

This above code works fine but I am trying to replace the numbers with words, for example;

Please Login or Register  to view this content.

...but this fails with the error "Run-time error 13 .... Type mismatch"

How can I use words within the Sumproduct coding..??


Thanks.

[zbor]

Try this:

Please Login or Register  to view this content.

[mowens74]

Zbor,

Thanks for this but it didn't work. If I enter as you have it above I get a "Compile error .... Expected: list separator or)" error MSG when moving away from the line. If I remove the quotations from around the words, I then get the "Run-time error 13 .... Type mismatch" error when the script is run.

Could you please explain what the '--' part of the equation does, seen it used a few times but cannot find the meaning.

[zbor]

Sorry, I forgot to put double quotation marks.
Does this helps? (also try double quotes in previous example).

Please Login or Register  to view this content.

[mowens74]

Zbor,

Thanks .... I tried both ways with double quoations around the word .... The command with "(--(" worked but your last command did not.

Please could you explain the meaning / significance of the "(--(" in the command.


Thank you.

[zbor]

Sorry. I didn't forgot, I just didn't had time yesterday. So here is explanation.

-- is called double unary operator and it's used in excel to convert TRUE/FALSE into 1/0.
It's similliar to 1* or 0+ but it has some difference.

If you have TRUE/FALSE in the cell, to convert it to number you can use:
=1*TRUE (result 1)
=0+TRUE (result 1)
=-TRUE (result -1 so you need --TRUE to get result 1)

I've put attacment so I will refer to it.

With data like:

Please Login or Register  to view this content.

In formula it look like this:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

You can see a step where TRUE/FALSE is converted into 1/0. Without -- you would get result 0.

Only difference is where you have non-numeric value in 2nd column then multiplying * return error (because you can not mutliply number with a text).
And -- ignore number (it act more like a SUM function when you select numbers and text with SUM function).

So from this data:

Please Login or Register  to view this content.

A fucntion with * will return:

Please Login or Register  to view this content.

And that's what happend in your code.

By double unary operator (--) you get:

Please Login or Register  to view this content.

Bearing in mind the above analysis USUALLY it's better to use double unary operator than mutliplying.
I emphasize usually, because there are cases when you must use first approach.
In other cases, you might want to return error if in 2nd column is text. You will use first approach again.

None of them is better - it's just metter of what you want in case when in 2nd column is entered non-numeric value.
More likely you would like to have result as a number, that's why I said usually is better to use second approach.