As previously stated, Sumproduct does not return an array, so
arrnum = Evaluate("=SUMPRODUCT((AGTHistory!A1:A11000=A" & 17 + J & _
")*(AGTHistory!C1:C11000=Main!B5),AGTHistory!K1:K1 1000)")

Worksheets("main").Range("B18:B29").Value = arrnum

Will never work for a changing value of J. It will place the same number in
B18:B29 if that is what is desired.

For some sample tested code:

Sub AAAB()
For j = 1 To 12
arrNum = Evaluate("=SUMPRODUCT((AGTHistory!A1:A11000=A" & 17 + j & _
")*(AGTHistory!C1:C11000=Main!B5),AGTHistory!K1:K1 1000)")
'Range("B" & 17 + j).Value = arrNum
Next
Range("B18:B28").Value = arrNum

End Sub

put in the same number in every cell in B17:B26

Sub AAA()
For j = 0 To 12
arrNum = Evaluate("=SUMPRODUCT((AGTHistory!A1:A11000=A" & 17 + j & _
")*(AGTHistory!C1:C11000=Main!B5),AGTHistory!K1:K1 1000)")
Range("B" & 17 + j).Value = arrNum
Next

End Sub

If the OP wants to generate all the answers in an array an place them all at
once

Sub AAA()
Dim arrnum(1 to 12,1 to 1) as Long
For j = 0 To 12
arrnum(j,1) = Evaluate("=SUMPRODUCT((AGTHistory!A1:A11000=A" & 17 + j & _
")*(AGTHistory!C1:C11000=Main!B5),AGTHistory!K1:K1 1000)")
Next
Range("B18:B29").Value = arrnum
End sub

--
Regards,
Tom Ogilvy


Put in the correct numbers (different in each cell). As I said, sumproduct
returns a single number, not an array.
"Toppers" wrote in message
...
Hi,
Gone full circle:

arrnum = Evaluate("=SUMPRODUCT((AGTHistory!A1:A11000=A" & 17 + J & _
")*(AGTHistory!C1:C11000=Main!B5),AGTHistory!K1:K1 1000)")

Worksheets("main").Range("B18:B29").Value = arrnum

the { } in the original formula were the problem!

"ram" wrote:

This seems to be pasting the formula and I only want it to paste the
values


"Toppers" wrote:

Hi again,

I tried this with data and computed a non-zero result


arrnum = "=SUMPRODUCT((AGTHistory!A1:A11000=A" & 17 + J & _

")*(AGTHistory!C1:C11000=Main!B5),AGTHistory!K1:K1 1000)"

Worksheets("main").Range("B18:B29").Formula = arrnum

"ram" wrote:

Can someone tell me why this code returns the #value! error


Dim J As Integer, val As Integer, arrnum As Variant



arrnum = Evaluate("{=SUMPRODUCT((AGTHistory!A1:A11000=A" & 17 + J
& _

")*(AGTHistory!C1:C11000=Main!B5),AGTHistory!K1:K1 1000)}")



Thanks for any help

This questions is related to my previous post


Worksheets("main").Range("B18:B29").Value = arrnum