Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
More help with Sumproduct VBA
Hi guys (and gals),
I have this: MsgBox Evaluate("SUMPRODUCT((IndivStats!L5:L65536=""Bosto n Garden {1}"")*(IndivStats!S5:S65536))") Thanks Tom. What I can't figure out........ Dim MyString as String MyString = "Boston Garden {1}" How do I subtitute MyString in the above formula??? I can't seem to get it to work. Thanks, John |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
More help with Sumproduct VBA
John,
Without testing: MsgBox Evaluate("SUMPRODUCT((IndivStats!L5:L65536=""" & MyString & """)*(IndivStats!S5:S65536))") Rob "John Wilson" wrote in message ... Hi guys (and gals), I have this: MsgBox Evaluate("SUMPRODUCT((IndivStats!L5:L65536=""Bosto n Garden {1}"")*(IndivStats!S5:S65536))") Thanks Tom. What I can't figure out........ Dim MyString as String MyString = "Boston Garden {1}" How do I subtitute MyString in the above formula??? I can't seem to get it to work. Thanks, John |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
More help with Sumproduct VBA
Maybe try to use chr(34) to enter the quotes? That is, if you just try
to enter the string w/o adding the quotes, you'll get an error I guess. So it would be chr(34) & MyString & chr(34)...etc. Hope that works :) John Wilson wrote: Hi guys (and gals), I have this: MsgBox Evaluate("SUMPRODUCT((IndivStats!L5:L65536=""Bosto n Garden {1}"")*(IndivStats!S5:S65536))") Thanks Tom. What I can't figure out........ Dim MyString as String MyString = "Boston Garden {1}" How do I subtitute MyString in the above formula??? I can't seem to get it to work. Thanks, John |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
More help with Sumproduct VBA
Rob & Kevin,
Both ways worked. Thanks. And it was Dave who gave me the original formula. Thanks Dave. John "Kevin T. Ryan" wrote in message ... Maybe try to use chr(34) to enter the quotes? That is, if you just try to enter the string w/o adding the quotes, you'll get an error I guess. So it would be chr(34) & MyString & chr(34)...etc. Hope that works :) John Wilson wrote: Hi guys (and gals), I have this: MsgBox Evaluate("SUMPRODUCT((IndivStats!L5:L65536=""Bosto n Garden {1}"")*(IndivStats!S5:S65536))") Thanks Tom. What I can't figure out........ Dim MyString as String MyString = "Boston Garden {1}" How do I subtitute MyString in the above formula??? I can't seem to get it to work. Thanks, John |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
More help with Sumproduct VBA
Another way:
MyString = """Boston Garden {1}""" ? mystring "Boston Garden {1}" ? "SUMPRODUCT((IndivStats!L5:L65536=" & myString & ")*(IndivStats!S5:S65536))" SUMPRODUCT((IndivStats!L5:L65536="Boston Garden {1}")*(IndivStats!S5:S65536)) -- Regards, Tom Ogilvy John Wilson wrote in message ... Hi guys (and gals), I have this: MsgBox Evaluate("SUMPRODUCT((IndivStats!L5:L65536=""Bosto n Garden {1}"")*(IndivStats!S5:S65536))") Thanks Tom. What I can't figure out........ Dim MyString as String MyString = "Boston Garden {1}" How do I subtitute MyString in the above formula??? I can't seem to get it to work. Thanks, John |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
More help with Sumproduct VBA
Tom,
That works too, but I like this better: Sub MySub() On Error GoTo DumbMistake ' my code here Exit Sub DumbMistake: MsgBox "How long is going to take for me to remember" & vbcrlf & _ "that I have to enclose a quote within quotes if I want" & vbcrlf & _ "the quote to appear in the cell formula from VBA?????" End Sub Thanks, John "Tom Ogilvy" wrote in message ... Another way: MyString = """Boston Garden {1}""" ? mystring "Boston Garden {1}" ? "SUMPRODUCT((IndivStats!L5:L65536=" & myString & ")*(IndivStats!S5:S65536))" SUMPRODUCT((IndivStats!L5:L65536="Boston Garden {1}")*(IndivStats!S5:S65536)) -- Regards, Tom Ogilvy John Wilson wrote in message ... Hi guys (and gals), I have this: MsgBox Evaluate("SUMPRODUCT((IndivStats!L5:L65536=""Bosto n Garden {1}"")*(IndivStats!S5:S65536))") Thanks Tom. What I can't figure out........ Dim MyString as String MyString = "Boston Garden {1}" How do I subtitute MyString in the above formula??? I can't seem to get it to work. Thanks, John |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
More help with Sumproduct VBA
John,
A platform independant (for the PC and Mac) way of doing a new line is vbNewLine instead of vbcrlf. It's easier to remember too. Rob "John Wilson" wrote in message ... Tom, That works too, but I like this better: Sub MySub() On Error GoTo DumbMistake ' my code here Exit Sub DumbMistake: MsgBox "How long is going to take for me to remember" & vbcrlf & _ "that I have to enclose a quote within quotes if I want" & vbcrlf & _ "the quote to appear in the cell formula from VBA?????" End Sub Thanks, John "Tom Ogilvy" wrote in message ... Another way: MyString = """Boston Garden {1}""" ? mystring "Boston Garden {1}" ? "SUMPRODUCT((IndivStats!L5:L65536=" & myString & ")*(IndivStats!S5:S65536))" SUMPRODUCT((IndivStats!L5:L65536="Boston Garden {1}")*(IndivStats!S5:S65536)) -- Regards, Tom Ogilvy John Wilson wrote in message ... Hi guys (and gals), I have this: MsgBox Evaluate("SUMPRODUCT((IndivStats!L5:L65536=""Bosto n Garden {1}"")*(IndivStats!S5:S65536))") Thanks Tom. What I can't figure out........ Dim MyString as String MyString = "Boston Garden {1}" How do I subtitute MyString in the above formula??? I can't seem to get it to work. Thanks, John |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
More help with Sumproduct VBA
Rob,
Never knew about the vbNewLine. Thanks. My final code (for the record and so that I might find it Google when I'm having senoir moments (sometimes days)) is as follows: CurrIndivScore = Evaluate("SUMPRODUCT((IndivStats!" & _ TeamColLtr & "5:" & TeamColLtr & BotRow & _ "=""" & CurrTeam & """)*(IndivStats!" & _ WeekColLtr & "5:" & WeekColLtr & BotRow & "))") Those quote marks can be a &%$*@# nightmare. Thanks to all who helped, John "Rob van Gelder" wrote in message ... John, A platform independant (for the PC and Mac) way of doing a new line is vbNewLine instead of vbcrlf. It's easier to remember too. Rob "John Wilson" wrote in message ... Tom, That works too, but I like this better: Sub MySub() On Error GoTo DumbMistake ' my code here Exit Sub DumbMistake: MsgBox "How long is going to take for me to remember" & vbcrlf & _ "that I have to enclose a quote within quotes if I want" & vbcrlf & _ "the quote to appear in the cell formula from VBA?????" End Sub Thanks, John "Tom Ogilvy" wrote in message ... Another way: MyString = """Boston Garden {1}""" ? mystring "Boston Garden {1}" ? "SUMPRODUCT((IndivStats!L5:L65536=" & myString & ")*(IndivStats!S5:S65536))" SUMPRODUCT((IndivStats!L5:L65536="Boston Garden {1}")*(IndivStats!S5:S65536)) -- Regards, Tom Ogilvy John Wilson wrote in message ... Hi guys (and gals), I have this: MsgBox Evaluate("SUMPRODUCT((IndivStats!L5:L65536=""Bosto n Garden {1}"")*(IndivStats!S5:S65536))") Thanks Tom. What I can't figure out........ Dim MyString as String MyString = "Boston Garden {1}" How do I subtitute MyString in the above formula??? I can't seem to get it to work. Thanks, John |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
More help with Sumproduct VBA
Sometimes a constant can help with the nightma
Const csQQ As String = """" Dim sRng1Addr As String Dim sRng2Addr As String With Sheets("IndivStats") sRng1Addr = .Name & "!" & .Range( _ TeamColLtr & "5").Resize(BotRow - 4).Address(False, False) sRng2Addr = .Name & "!" & .Range( _ WeekColLtr & "5").Resize(BotRow - 4).Address(False, False) End With CurrIndivScore = Evaluate("SumProduct((" & sRng1Addr & "=" & _ csQQ & CurrTeam & csQQ & ")*(" & sRng2Addr & "))") In article , "John Wilson" wrote: Rob, Never knew about the vbNewLine. Thanks. My final code (for the record and so that I might find it Google when I'm having senoir moments (sometimes days)) is as follows: CurrIndivScore = Evaluate("SUMPRODUCT((IndivStats!" & _ TeamColLtr & "5:" & TeamColLtr & BotRow & _ "=""" & CurrTeam & """)*(IndivStats!" & _ WeekColLtr & "5:" & WeekColLtr & BotRow & "))") Those quote marks can be a &%$*@# nightmare. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
More help with Sumproduct VBA
J.E.
Soooooooo many different ways and I couldn't find one of them on my own. :-( I can't even remember how to spell "senior" correctly. Thanks, John "JE McGimpsey" wrote in message ... Sometimes a constant can help with the nightma Const csQQ As String = """" Dim sRng1Addr As String Dim sRng2Addr As String With Sheets("IndivStats") sRng1Addr = .Name & "!" & .Range( _ TeamColLtr & "5").Resize(BotRow - 4).Address(False, False) sRng2Addr = .Name & "!" & .Range( _ WeekColLtr & "5").Resize(BotRow - 4).Address(False, False) End With CurrIndivScore = Evaluate("SumProduct((" & sRng1Addr & "=" & _ csQQ & CurrTeam & csQQ & ")*(" & sRng2Addr & "))") In article , "John Wilson" wrote: Rob, Never knew about the vbNewLine. Thanks. My final code (for the record and so that I might find it Google when I'm having senoir moments (sometimes days)) is as follows: CurrIndivScore = Evaluate("SUMPRODUCT((IndivStats!" & _ TeamColLtr & "5:" & TeamColLtr & BotRow & _ "=""" & CurrTeam & """)*(IndivStats!" & _ WeekColLtr & "5:" & WeekColLtr & BotRow & "))") Those quote marks can be a &%$*@# nightmare. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
More help with Sumproduct VBA
Soooo many ways to introduce subtle failures, too:
sRng1Addr = .Name & "!" & .Range( _ TeamColLtr & "5").Resize(BotRow - 4).Address(False, False) sRng2Addr = .Name & "!" & .Range( _ WeekColLtr & "5").Resize(BotRow - 4).Address(False, False) would be much better as sRng1Addr = "'" & .Name & "'!" & .Range( _ TeamColLtr & "5").Resize(BotRow - 4).Address(False, False) sRng2Addr = "'" & .Name & "'!" & .Range( _ WeekColLtr & "5").Resize(BotRow - 4).Address(False, False) In article , "John Wilson" wrote: Soooooooo many different ways and I couldn't find one of them on my own. :-( I can't even remember how to spell "senior" correctly. Thanks, John "JE McGimpsey" wrote in message ... Sometimes a constant can help with the nightma Const csQQ As String = """" Dim sRng1Addr As String Dim sRng2Addr As String With Sheets("IndivStats") sRng1Addr = .Name & "!" & .Range( _ TeamColLtr & "5").Resize(BotRow - 4).Address(False, False) sRng2Addr = .Name & "!" & .Range( _ WeekColLtr & "5").Resize(BotRow - 4).Address(False, False) End With CurrIndivScore = Evaluate("SumProduct((" & sRng1Addr & "=" & _ csQQ & CurrTeam & csQQ & ")*(" & sRng2Addr & "))") |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
More help with Sumproduct VBA
Thanks - I forget that that option's available...
In article , Dave Peterson wrote: And even let excel do the work: sRng1Addr = .range(....).address(external:=true) |
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) | |||
SUMPRODUCT....and then some! | Excel Worksheet Functions | |||
sumproduct | Excel Discussion (Misc queries) | |||
Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions |