Excel XP VBA Array formula
 

The first two sub procedures below work OK

The third one however, produces a Type Mismatch error.

Do VBA Array formulas not accept variable arguments or am I miss-keying
something?

All help gratefully accepted. :-)

Dim rep As String
Dim pco As String

Sub addpco()

Range("K1") = "005522"
Range("L1") = "PRI"

rep = "005522"
pco = "PRI"

TotalPRI = [Sum((A2:A14=K1)*(D2:D14=L1)*(G2:G14))]
MsgBox TotalPRI

TotalPRI = [Sum((A2:A14="005522")*(D2:D14="PRI")*(G2:G14))]
MsgBox TotalPRI

TotalPRI = [Sum((A2:A14=rep)*(D2:D14=pco)*(G2:G14))]
MsgBox TotalPRI

End Sub


Thanks in Anticipation

John Howard
Sydney, Australia

Excel XP VBA Array formula
 

[ ] notation for the Evaluate method requires a string between the [] rather
than a mixture of string and VBA variables.
If you want to construct the string using VBA you should use
Application.Evaluate or worksheet.Evaluate rather than []

Also you need to be careful when using Application.Evaluate with unqualified
range references like D2:D14 because they refer to whatever happens to be
the active worksheet at evaluation time: safer to use worksheet.evaluate or
qualified range references.

Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com

"SMS - John Howard" wrote in message
...
The first two sub procedures below work OK

The third one however, produces a Type Mismatch error.

Do VBA Array formulas not accept variable arguments or am I miss-keying
something?

All help gratefully accepted. :-)

Dim rep As String
Dim pco As String

Sub addpco()

Range("K1") = "005522"
Range("L1") = "PRI"

rep = "005522"
pco = "PRI"

TotalPRI = [Sum((A2:A14=K1)*(D2:D14=L1)*(G2:G14))]
MsgBox TotalPRI

TotalPRI = [Sum((A2:A14="005522")*(D2:D14="PRI")*(G2:G14))]
MsgBox TotalPRI

TotalPRI = [Sum((A2:A14=rep)*(D2:D14=pco)*(G2:G14))]
MsgBox TotalPRI

End Sub


Thanks in Anticipation

John Howard
Sydney, Australia

Excel XP VBA Array formula
 

Thanks Charles.
I will experiment around your suggestions.
"Charles Williams" wrote in message
...
[ ] notation for the Evaluate method requires a string between the []
rather
than a mixture of string and VBA variables.
If you want to construct the string using VBA you should use
Application.Evaluate or worksheet.Evaluate rather than []

Also you need to be careful when using Application.Evaluate with
unqualified
range references like D2:D14 because they refer to whatever happens to be
the active worksheet at evaluation time: safer to use worksheet.evaluate
or
qualified range references.

Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com

"SMS - John Howard" wrote in message
...
The first two sub procedures below work OK

The third one however, produces a Type Mismatch error.

Do VBA Array formulas not accept variable arguments or am I miss-keying
something?

All help gratefully accepted. :-)

Dim rep As String
Dim pco As String

Sub addpco()

Range("K1") = "005522"
Range("L1") = "PRI"

rep = "005522"
pco = "PRI"

TotalPRI = [Sum((A2:A14=K1)*(D2:D14=L1)*(G2:G14))]
MsgBox TotalPRI

TotalPRI = [Sum((A2:A14="005522")*(D2:D14="PRI")*(G2:G14))]
MsgBox TotalPRI

TotalPRI = [Sum((A2:A14=rep)*(D2:D14=pco)*(G2:G14))]
MsgBox TotalPRI

End Sub


Thanks in Anticipation

John Howard
Sydney, Australia