How to use Sumproduct in VBA???
 

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

How to use Sumproduct in VBA???
 

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

On 31 Dec, 09:36, FARAZ QURESHI
wrote:
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

How to use Sumproduct in VBA???
 

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)

"RichardSchollar" wrote in message
...
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

On 31 Dec, 09:36, FARAZ QURESHI
wrote:
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

How to use Sumproduct in VBA???
 

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!

"RichardSchollar" wrote:

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

On 31 Dec, 09:36, FARAZ QURESHI
wrote:
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