I appreciate all the help:


I have two sheets

I was using the sumproduct function in each of the cells on my main sheet
to return the totals based on the date and agent ID. However when I enter a
new ID it takes a long time to update. So I wanted to replace all the
sumproduct formulas with just the values. The code below accomplishes this
for me. However it will place the value in each cell which is slowing the
program down. I would like it to find the total for each cell and past them
all at the same time. Thanks for any help


'moves date from main sheet to the agent history sheet
Dim agtdetailnew As Range, csonew As Range, repnew As Range, othernew As
Range, agtdetailhistory As Range, otherhistory As Range, csohistory As Range,
rephistory As Range
If ActiveSheet.Range("b3") = "" Then
Range("b3").Select
MsgBox "Please Enter Date"
Range("b1").Select
Else
Set agtdetailnew = Worksheets("main").Range("b3:b8")
Set othernew = Worksheets("main").Range("d4:d7")
Set csonew = Worksheets("main").Range("f4:f13")
Set repnew = Worksheets("main").Range("h4:h13")
Set agtdetailhistory = Worksheets("agthistory").Cells(Rows.Count,
"a").End(xlUp)(2)
Set otherhistory = Worksheets("agthistory").Cells(Rows.Count,
"a").End(xlUp)(2).Offset(0, 6)
Set csohistory = Worksheets("agthistory").Cells(Rows.Count,
"a").End(xlUp)(2).Offset(0, 10)
Set rephistory = Worksheets("agthistory").Cells(Rows.Count,
"a").End(xlUp)(2).Offset(0, 20)
'Range("a1:h1") = WorksheetFunction.Transpose(Range("a1:a8"))
agtdetailnew.Copy
agtdetailhistory.PasteSpecial 12, Transpose:=True
othernew.Copy
otherhistory.PasteSpecial 12, Transpose:=True
csonew.Copy
csohistory.PasteSpecial 12, Transpose:=True
repnew.Copy
rephistory.PasteSpecial 12, Transpose:=True
Range("b1").Select
End If
'Retrives the totals from agent history sheet and places the value on
the main sheet
Dim j As Integer

For j = 1 To 12

Worksheets("main").Range("B" & 17 + j).Value =
Evaluate("=SUMPRODUCT((AGTHistory!A1:A11000=A" & 17 + j &
")*(AGTHistory!C1:C11000=Main!B5),AGTHistory!K1:K1 1000)")
Worksheets("main").Range("C" & 17 + j).Value =
Evaluate("=SUMPRODUCT((AGTHistory!A1:A11000=A" & 17 + j &
")*(AGTHistory!C1:C11000=Main!B5),AGTHistory!L1:L1 1000)")
Worksheets("main").Range("D" & 17 + j).Value =
Evaluate("=SUMPRODUCT((AGTHistory!A1:A11000=A" & 17 + j &
")*(AGTHistory!C1:C11000=Main!B5),AGTHistory!M1:M1 1000)")
Worksheets("main").Range("E" & 17 + j).Value =
Evaluate("=SUMPRODUCT((AGTHistory!A1:A11000=A" & 17 + j &
")*(AGTHistory!C1:C11000=Main!B5),AGTHistory!N1:N1 1000)")
Worksheets("main").Range("F" & 17 + j).Value =
Evaluate("=SUMPRODUCT((AGTHistory!A1:A11000=A" & 17 + j &
")*(AGTHistory!C1:C11000=Main!B5),AGTHistory!O1:O1 1000)")
Worksheets("main").Range("G" & 17 + j).Value =
Evaluate("=SUMPRODUCT((AGTHistory!A1:A11000=A" & 17 + j &
")*(AGTHistory!C1:C11000=Main!B5),AGTHistory!P1:P1 1000)")
Worksheets("main").Range("H" & 17 + j).Value =
Evaluate("=SUMPRODUCT((AGTHistory!A1:A11000=A" & 17 + j &
")*(AGTHistory!C1:C11000=Main!B5),AGTHistory!Q1:Q1 1000)")
Worksheets("main").Range("I" & 17 + j).Value =
Evaluate("=SUMPRODUCT((AGTHistory!A1:A11000=A" & 17 + j &
")*(AGTHistory!C1:C11000=Main!B5),AGTHistory!R1:R1 1000)")
Worksheets("main").Range("J" & 17 + j).Value =
Evaluate("=SUMPRODUCT((AGTHistory!A1:A11000=A" & 17 + j &
")*(AGTHistory!C1:C11000=Main!B5),AGTHistory!S1:S1 1000)")
Worksheets("main").Range("K" & 17 + j).Value =
Evaluate("=SUMPRODUCT((AGTHistory!A1:A11000=A" & 17 + j &
")*(AGTHistory!C1:C11000=Main!B5),AGTHistory!T1:T1 1000)")
Next j

Thanks for any help


"Tom Ogilvy" wrote:

Sumproduct evaluates to a single number. arrnum is not an array, but a
single value. Not sure what the intent is to assign it to multiple cells.

--
Regards,
Tom Ogilvy


"Toppers" wrote in message
...
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