sumproduct in VBA

[ghobbit]

Hi

I have two columns on which I want to run a sumproduct formula using
VBA. Column D has names and column O has what I want to match it with.
I have two cells containing the information for the criteria namely Q3
= John and S1 = AGREE. I would like the result put into S2

I want to run this using a command button rather than have the formula
in the cell.

If I put this formula into the cell S2 then it works fine

=SUMPRODUCT((D22500=Q3)*(O2:O2500=S1))

However if I create a button and have this code, I get a #Value error
in S3 instead of the result

Private Sub CommandButton2_Click()

Cells(3, "S").value =
Evaluate("=SUMPRODUCT((D22500=Q3)*(O2:02500=S1))")

End Sub

If I leave out the evaluate and just have

Cells(3, "S").value =SUMPRODUCT((D22500=Q3)*(O2:02500=S1))

then I get a syntax error

Is there anything obvious I'm doing wrong??

many thanks

Steve

 

[Don Guillett]

try

Evaluate("=SUMPRODUCT((D22500=Q3)*(O2:02500=S1))")
Evaluate("=SUMPRODUCT((D22500=range("Q3"))*(O2:02500=range("S1")))")

 

[Peter T]

Is there anything obvious I'm doing wrong??

an un-obvious typo, change

O2:02500=S1
to
O22500=S1

Regards,
Peter T

 

[ghobbit]

Hi Don and Pete

Thanks for your replies

Public Sub CommandButton2_Click()

Cells(3, "S").Value =
Evaluate("=SUMPRODUCT((D22500=range("Q3"))*(O2:O2500=range("S1")))")

End Sub

gives me a syntax error when I click on the button with the Public Sub
CommandButton2_Click() bit highlighted in yellow

and if I delete the last parentheses it gives me a compile error with
Q3 highlighted saying expected list seperator or ). So I put the
parentheses back only to get the same error.

and as far as I can see there are no typo's

This is really proving quite frustrating because it looks like it
should work and the formula does work if I put it in the cell but I
really dont want to to do this.

any idea?

many thanks for your input

regards

Steve

 

[Peter T]

and as far as I can see there are no typo's

Well there's a typo in the code you posted, if corrected as I suggested the
formula works

still can't see it - try this

Evaluate("=SUMPRODUCT((D22500=Q3)*(O2:O2500=S1))")

(copy & paste it)

don't really need the "=" before Sumproduct, but it works either way

Regards,
Peter T

 

[Don Guillett]

sorry, try it this way
Evaluate("=SUMPRODUCT((D22500=Q3)*(O2:O2500=S1))")

 

[ghobbit]

Thanks Peter

Couldnt see it for looking

works fine now

regards

steve