Sumproduct problem
 

I am trying to use an array value in a sumprodct function using Evaluate
under VBA My code looks like this:
Evaluate("=SUMPRODUCT(--(Arr(i)" & _
"Billable FT'!" & CntRef.Address & """0""),--('Arr(i)" & _
"Billable FT'!" & CntRef2.Address)

Where CntRef is a range and both are the same size, "Arr(i)" is the array. I
think I have all the right Quotes and &'s. I get a #Value Error when I run
the code

Help

Sumproduct problem
 

Helen,

SUMPRODUCT is a worksheet function. The array as you have it is a VBA v
ariable. You cannot plug the latter directly into the former.

If array(i) holds say asheet reference or a workbook name, you need the
formula to resolve, so that means in the VBA, not in the SUMPRODUCT formula,
maybe like

Evaluate("=SUMPRODUCT(--(" & Arr(i) & _
"Billable FT'!" & CntRef.Address & """0""),--('" & Arr(i) & _
"Billable FT'!" & CntRef2.Address)

--
HTH

Bob Phillips

"Helen" wrote in message
...
I am trying to use an array value in a sumprodct function using Evaluate
under VBA My code looks like this:
Evaluate("=SUMPRODUCT(--(Arr(i)" & _
"Billable FT'!" & CntRef.Address & """0""),--('Arr(i)" & _
"Billable FT'!" & CntRef2.Address)

Where CntRef is a range and both are the same size, "Arr(i)" is the array.
I
think I have all the right Quotes and &'s. I get a #Value Error when I run
the code

Help

Sumproduct problem
 

Assuming Arr(i) holds some type of qualifier that completes a sheetname

Evaluate("=SUMPRODUCT(--('" & Arr(i) & _
"Billable FT'!" & CntRef.Address & """0""),--('" & Arr(i) & _
"Billable FT'!" & CntRef2.Address & "))")

Now benchtest it in the immediate window:

arr = Array("My","Your")
i = 1
? arr(i)
Your
set cntRef = Range("A1:A10")
set cntRef2 = Range("B1:B10")
? "=SUMPRODUCT(--('" & Arr(i) & _
"Billable FT'!" & CntRef.Address & """0""),--('" & Arr(i) & _
"Billable FT'!" & CntRef2.Address & "))"
=SUMPRODUCT(--('YourBillable FT'!$A$1:$A$10"0"),--('YourBillable
FT'!$B$1:$B$10))

That should be pretty close.

--
Regards,
Tom Ogilvy




"Helen" wrote in message
...
I am trying to use an array value in a sumprodct function using Evaluate
under VBA My code looks like this:
Evaluate("=SUMPRODUCT(--(Arr(i)" & _
"Billable FT'!" & CntRef.Address & """0""),--('Arr(i)" & _
"Billable FT'!" & CntRef2.Address)

Where CntRef is a range and both are the same size, "Arr(i)" is the array.
I
think I have all the right Quotes and &'s. I get a #Value Error when I run
the code

Help

Sumproduct problem
 

Just a heads up to the OP. Bob gave an excellent explanation of the
immediate issue, but there are additional flaws in your original formula.
See my post for additional considerations.



--
regards,
Tom Ogilvy

"Bob Phillips" wrote in message
...
Helen,

SUMPRODUCT is a worksheet function. The array as you have it is a VBA v
ariable. You cannot plug the latter directly into the former.

If array(i) holds say asheet reference or a workbook name, you need the
formula to resolve, so that means in the VBA, not in the SUMPRODUCT
formula,
maybe like

Evaluate("=SUMPRODUCT(--(" & Arr(i) & _
"Billable FT'!" & CntRef.Address & """0""),--('" & Arr(i) &
_
"Billable FT'!" & CntRef2.Address)

--
HTH

Bob Phillips

"Helen" wrote in message
...
I am trying to use an array value in a sumprodct function using Evaluate
under VBA My code looks like this:
Evaluate("=SUMPRODUCT(--(Arr(i)" & _
"Billable FT'!" & CntRef.Address & """0""),--('Arr(i)" & _
"Billable FT'!" & CntRef2.Address)

Where CntRef is a range and both are the same size, "Arr(i)" is the
array.
I
think I have all the right Quotes and &'s. I get a #Value Error when I
run
the code

Help

Sumproduct problem
 

based on your later posting example of

Cells(49, C) = Workbooks(WrkBk).Worksheets("Billable
FT").WorksheetFunction.Subtotal(9, xlRng)

I hope Arr(i) holds something like [Mybook1.xls]
including the square brackets.

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
Assuming Arr(i) holds some type of qualifier that completes a sheetname

Evaluate("=SUMPRODUCT(--('" & Arr(i) & _
"Billable FT'!" & CntRef.Address & """0""),--('" & Arr(i) &
_
"Billable FT'!" & CntRef2.Address & "))")

Now benchtest it in the immediate window:

arr = Array("My","Your")
i = 1
? arr(i)
Your
set cntRef = Range("A1:A10")
set cntRef2 = Range("B1:B10")
? "=SUMPRODUCT(--('" & Arr(i) & _
"Billable FT'!" & CntRef.Address & """0""),--('" & Arr(i) &
_
"Billable FT'!" & CntRef2.Address & "))"
=SUMPRODUCT(--('YourBillable FT'!$A$1:$A$10"0"),--('YourBillable
FT'!$B$1:$B$10))

That should be pretty close.

--
Regards,
Tom Ogilvy




"Helen" wrote in message
...
I am trying to use an array value in a sumprodct function using Evaluate
under VBA My code looks like this:
Evaluate("=SUMPRODUCT(--(Arr(i)" & _
"Billable FT'!" & CntRef.Address & """0""),--('Arr(i)" & _
"Billable FT'!" & CntRef2.Address)

Where CntRef is a range and both are the same size, "Arr(i)" is the
array.
I
think I have all the right Quotes and &'s. I get a #Value Error when I
run
the code

Help