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((D2:D2500=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((D2:D2500=Q3)*(O2:02500=S1)) ")

End Sub

If I leave out the evaluate and just have

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

then I get a syntax error

Is there anything obvious I'm doing wrong??

many thanks

Steve


--
ghobbit
------------------------------------------------------------------------
ghobbit's Profile: http://www.excelforum.com/member.php...o&userid=12385
View this thread: http://www.excelforum.com/showthread...hreadid=544477

try

Evaluate("=SUMPRODUCT((D2:D2500=Q3)*(O2:02500=S1)) ")
Evaluate("=SUMPRODUCT((D2:D2500=range("Q3"))*(O2:0 2500=range("S1")))")

--
Don Guillett
SalesAid Software

"ghobbit" wrote in
message ...

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((D2:D2500=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((D2:D2500=Q3)*(O2:02500=S1)) ")

End Sub

If I leave out the evaluate and just have

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

then I get a syntax error

Is there anything obvious I'm doing wrong??

many thanks

Steve


--
ghobbit
------------------------------------------------------------------------
ghobbit's Profile:
http://www.excelforum.com/member.php...o&userid=12385
View this thread: http://www.excelforum.com/showthread...hreadid=544477

Is there anything obvious I'm doing wrong??

an un-obvious typo, change

O2:02500=S1
to
O2:o2500=S1

Regards,
Peter T

"ghobbit" wrote in
message ...

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((D2:D2500=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((D2:D2500=Q3)*(O2:02500=S1)) ")

End Sub

If I leave out the evaluate and just have

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

then I get a syntax error

Is there anything obvious I'm doing wrong??

many thanks

Steve


--
ghobbit
------------------------------------------------------------------------
ghobbit's Profile:
http://www.excelforum.com/member.php...o&userid=12385
View this thread: http://www.excelforum.com/showthread...hreadid=544477

Hi Don and Pete

Thanks for your replies

Public Sub CommandButton2_Click()

Cells(3, "S").Value =
Evaluate("=SUMPRODUCT((D2:D2500=range("Q3"))*(O2:O 2500=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


--
ghobbit
------------------------------------------------------------------------
ghobbit's Profile: http://www.excelforum.com/member.php...o&userid=12385
View this thread: http://www.excelforum.com/showthread...hreadid=544477

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((D2:D2500=Q3)*(O2:O2500=S1)) ")

(copy & paste it)

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

Regards,
Peter T


"ghobbit" wrote in
message ...

Hi Don and Pete

Thanks for your replies

Public Sub CommandButton2_Click()

Cells(3, "S").Value =
Evaluate("=SUMPRODUCT((D2:D2500=range("Q3"))*(O2:O 2500=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


--
ghobbit
------------------------------------------------------------------------
ghobbit's Profile:
http://www.excelforum.com/member.php...o&userid=12385
View this thread: http://www.excelforum.com/showthread...hreadid=544477

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


--
Don Guillett
SalesAid Software

"ghobbit" wrote in
message ...

Hi Don and Pete

Thanks for your replies

Public Sub CommandButton2_Click()

Cells(3, "S").Value =
Evaluate("=SUMPRODUCT((D2:D2500=range("Q3"))*(O2:O 2500=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


--
ghobbit
------------------------------------------------------------------------
ghobbit's Profile:
http://www.excelforum.com/member.php...o&userid=12385
View this thread: http://www.excelforum.com/showthread...hreadid=544477

Thanks Peter

Couldnt see it for looking

works fine now

regards

steve


--
ghobbit
------------------------------------------------------------------------
ghobbit's Profile: http://www.excelforum.com/member.php...o&userid=12385
View this thread: http://www.excelforum.com/showthread...hreadid=544477