Sumproduct in VBA

Hi there,

Got two problems in one little segment of code!

I want to enter the IF statement "If" output is "M1" or "M2" what syntax should I be using.

Also I want to be able to use SUMPRODUCT in VBA but it does not seem to work the program just stops when I try to call it? Again what syntax should I use?

Code:

---

If output = ("M1" Or "M") Then
Dim Prob() As Double
ReDim Prob(N)
For i = 1 To N
    Prob(i) = Application.Max(p2 ^ i * (1 - p2) ^ (N - i))
Next i
M1 = Application.SumProduct(Prob, Val)
M2 = Application.SumProduct(Prob, Val ^ 2)
End If

---

Thanks

Baz

Code:

---

If output = "M1" Or output = "M" Then

---

You cannot use SumProduct directly in VBA, you need to EVALUATE it.
Have a look here for more info.
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

With OR and AND functions, you need to test both indivifually like so

Code:

---

If output = "M1" Or output = "M" Then

---

SUMPRODUCT is an array formula, so you cannot use Applictaion, you have to evaluate it

Code:

---

M1 = Application.Evaluate("SumProduct(" & Prob & "," &  Val & ")")

---

Oopos, didn't see Malcolm's response.

thanks!

To all of you.

Baz