Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sumproduct VBA Conversion
Okay, so Sumproduct is not my forte.
I rarely use it. I have this formula on a worksheet: =SUMPRODUCT((L5:L1000="Boston Garden {2}")*(S5:S1000)) How can I convert this to VBA?? Thanks, John |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sumproduct VBA Conversion
you could loop through the cells or just let excel help your code:
MsgBox _ Application.Evaluate("SUMPRODUCT((L5:L1000=""Bosto n Garden {2}"")*(S5:S1000))") (kind of feels like cheating, though.) John Wilson wrote: Okay, so Sumproduct is not my forte. I rarely use it. I have this formula on a worksheet: =SUMPRODUCT((L5:L1000="Boston Garden {2}")*(S5:S1000)) How can I convert this to VBA?? Thanks, John -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sumproduct VBA Conversion
John
Try MsgBox Evaluate("=sumproduct((L5:L1000=""Boston Garden {2}"")*(S5:S1000))") Tony ----- John Wilson wrote: ----- Okay, so Sumproduct is not my forte. I rarely use it. I have this formula on a worksheet: =SUMPRODUCT((L5:L1000="Boston Garden {2}")*(S5:S1000)) How can I convert this to VBA?? Thanks, John |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sumproduct VBA Conversion
Dave,
When I looked at your reply, my first thought was "I already tried that 100 times in 100 different variations and I know it won't work!!!" But I copied and pasted your conversion in the immediate window just to prove my point and I'll be damned if it didn't give me exactly the result that I was looking for. How could that be?!?!?!?! I'm sure this won't be the last time that those double quotes will give me an Excedrin headache. Thanks a million, John "Dave Peterson" wrote in message ... you could loop through the cells or just let excel help your code: MsgBox _ Application.Evaluate("SUMPRODUCT((L5:L1000=""Bosto n Garden {2}"")*(S5:S1000))") (kind of feels like cheating, though.) John Wilson wrote: Okay, so Sumproduct is not my forte. I rarely use it. I have this formula on a worksheet: =SUMPRODUCT((L5:L1000="Boston Garden {2}")*(S5:S1000)) How can I convert this to VBA?? Thanks, John -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sumproduct VBA Conversion
Tony,
Thanks. Already rec'd the same answer from Dave. I neglected to notice those quote marks when I was trying to convert this. John "acw" wrote in message ... John Try MsgBox Evaluate("=sumproduct((L5:L1000=""Boston Garden {2}"")*(S5:S1000))") Tony ----- John Wilson wrote: ----- Okay, so Sumproduct is not my forte. I rarely use it. I have this formula on a worksheet: =SUMPRODUCT((L5:L1000="Boston Garden {2}")*(S5:S1000)) How can I convert this to VBA?? Thanks, John |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sumproduct VBA Conversion
When it gets really confusing, I'll use: chr(34) instead of """" (is that
enough of them???) John Wilson wrote: Dave, When I looked at your reply, my first thought was "I already tried that 100 times in 100 different variations and I know it won't work!!!" But I copied and pasted your conversion in the immediate window just to prove my point and I'll be damned if it didn't give me exactly the result that I was looking for. How could that be?!?!?!?! I'm sure this won't be the last time that those double quotes will give me an Excedrin headache. Thanks a million, John "Dave Peterson" wrote in message ... you could loop through the cells or just let excel help your code: MsgBox _ Application.Evaluate("SUMPRODUCT((L5:L1000=""Bosto n Garden {2}"")*(S5:S1000))") (kind of feels like cheating, though.) John Wilson wrote: Okay, so Sumproduct is not my forte. I rarely use it. I have this formula on a worksheet: =SUMPRODUCT((L5:L1000="Boston Garden {2}")*(S5:S1000)) How can I convert this to VBA?? Thanks, John -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct with Condition OR Sumproduct with ADDRESS function - HE | Excel Discussion (Misc queries) | |||
Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
Month Conversion in SUMPRODUCT | Excel Worksheet Functions | |||
Day Conversion | Excel Worksheet Functions | |||
ESN conversion | Excel Worksheet Functions |