Sumproduct problem

[Helen]

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

 

[Bob Phillips]

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)

 

[Tom Ogilvy]

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.

 

[Tom Ogilvy]

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.

 

[Tom Ogilvy]

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.