How to use Sumproduct in VBA???

[FARAZ QURESHI]

Hi friends,

I wanna know what's the problem with:

Public Function FHWA(One, Two)
FHWA=Application.Caller.Parent.Evaluate( _
"SUMPRODUCT("&One.Address&","&Two.Address&")")
End Function

The code gives a:
Compile Error
Expected: list separator or )

What would be the appropriate way to write a simple code for sumproduct of
two arrays named One & Two?

Thanx

 

[RichardSchollar]

Hi

Make sure you separate your concatenation operator (&) from the
variable names - the following works for me:

Public Function FHWA(One, Two)
FHWA = Application.Caller.Parent.Evaluate( _
"SUMPRODUCT(" & One.Address & "," & Two.Address & ")")
End Function

Hope this helps!

Richard

 

[Bob Phillips]

You might want some error handling

Public Function FHWA(One, Two)
If One.Cells.Count <> Two.Cells.Count Then
FHWA = "# mismatched ranges"
Else
FHWA = Application.Caller.Parent.Evaluate( _
"SUMPRODUCT(" & One.Address & "," & Two.Address & ")")
End If
End Function




--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Hi

Make sure you separate your concatenation operator (&) from the
variable names - the following works for me:

Public Function FHWA(One, Two)
FHWA = Application.Caller.Parent.Evaluate( _
"SUMPRODUCT(" & One.Address & "," & Two.Address & ")")
End Function

Hope this helps!

Richard

 

[FARAZ QURESHI]

Thanx Richard,

It was your example that clarified to me now that there should be a space
existing between the concatenating symbol and rest.

In other words:

"SUMPRODUCT(" & One.Address & "," & Two.Address & ")") IS CORRECT
"SUMPRODUCT("&One.Address&","&Two.Address&")") IS WRONG

Thanx again!