More help with Sumproduct VBA

J

John Wilson

Hi guys (and gals),

I have this:

MsgBox Evaluate("SUMPRODUCT((IndivStats!L5:L65536=""Boston 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
 
R

Rob van Gelder

John,

Without testing:

MsgBox Evaluate("SUMPRODUCT((IndivStats!L5:L65536=""" & MyString &
""")*(IndivStats!S5:S65536))")

Rob
 
K

Kevin T. Ryan

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 :)
 
J

John Wilson

Rob & Kevin,

Both ways worked. Thanks.

And it was Dave who gave me the original formula.
Thanks Dave.

John
 
T

Tom Ogilvy

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))
 
J

John Wilson

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
 
R

Rob van Gelder

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
 
J

John Wilson

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
 
J

JE McGimpsey

Sometimes a constant can help with the nightmare:

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 & "))")
 
J

John Wilson

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
 
J

JE McGimpsey

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)
 
Top