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

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

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

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

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

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

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

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

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.

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.

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

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)