|
sumproduct in VBA
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 |
sumproduct in VBA
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 |
sumproduct in VBA
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 |
sumproduct in VBA
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 |
sumproduct in VBA
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 |
sumproduct in VBA
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 |
| All times are GMT +1. The time now is 02:07 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com