|
Paste
Can someone show me how the results from the following code can be pasted all
at the same time instead of pasting in one cell at a time. Dim J As Integer For J = 1 To 2 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)") Next J End Sub Thanks for all your help |
Paste
I think this should do it
val = Evaluate("=SUMPRODUCT((AGTHistory!A1:A11000=A" & 17 + j & _ ")*(AGTHistory!C1:C11000=Main!B5),AGTHistory!K1:K1 1000)") Worksheets("main").Range("B18:C19").Value = val -- HTH Bob Phillips (remove nothere from email address if mailing direct) "ram" wrote in message ... Can someone show me how the results from the following code can be pasted all at the same time instead of pasting in one cell at a time. Dim J As Integer For J = 1 To 2 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)") Next J End Sub Thanks for all your help |
Paste
Hi Bob,
It returns the same number in all 4 cells and they should be differant. Date Sales Expenses Jan 1000 400 Feb 1500 300 Any suggestions Thanks Thanks "Bob Phillips" wrote: I think this should do it val = Evaluate("=SUMPRODUCT((AGTHistory!A1:A11000=A" & 17 + j & _ ")*(AGTHistory!C1:C11000=Main!B5),AGTHistory!K1:K1 1000)") Worksheets("main").Range("B18:C19").Value = val -- HTH Bob Phillips (remove nothere from email address if mailing direct) "ram" wrote in message ... Can someone show me how the results from the following code can be pasted all at the same time instead of pasting in one cell at a time. Dim J As Integer For J = 1 To 2 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)") Next J End Sub Thanks for all your help |
Paste
How about this then
Worksheets("main").Range("B18:C19").Formula = _ "=SUMPRODUCT((AGTHistory!$A$1:$A$11000=$A18)*" & _ "(AGTHistory!$C$1:$C$11000=Main!$B$5),AGTHistory!K $1:K$11000)" -- HTH Bob Phillips (remove nothere from email address if mailing direct) "ram" wrote in message ... Hi Bob, It returns the same number in all 4 cells and they should be differant. Date Sales Expenses Jan 1000 400 Feb 1500 300 Any suggestions Thanks Thanks "Bob Phillips" wrote: I think this should do it val = Evaluate("=SUMPRODUCT((AGTHistory!A1:A11000=A" & 17 + j & _ ")*(AGTHistory!C1:C11000=Main!B5),AGTHistory!K1:K1 1000)") Worksheets("main").Range("B18:C19").Value = val -- HTH Bob Phillips (remove nothere from email address if mailing direct) "ram" wrote in message ... Can someone show me how the results from the following code can be pasted all at the same time instead of pasting in one cell at a time. Dim J As Integer For J = 1 To 2 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)") Next J End Sub Thanks for all your help |
Paste
Hi Bob,
Your code is working however it it placing the formulas in the cells and i'm trying to only have the values entered. any suggestions Thanks in advance "Bob Phillips" wrote: How about this then Worksheets("main").Range("B18:C19").Formula = _ "=SUMPRODUCT((AGTHistory!$A$1:$A$11000=$A18)*" & _ "(AGTHistory!$C$1:$C$11000=Main!$B$5),AGTHistory!K $1:K$11000)" -- HTH Bob Phillips (remove nothere from email address if mailing direct) "ram" wrote in message ... Hi Bob, It returns the same number in all 4 cells and they should be differant. Date Sales Expenses Jan 1000 400 Feb 1500 300 Any suggestions Thanks Thanks "Bob Phillips" wrote: I think this should do it val = Evaluate("=SUMPRODUCT((AGTHistory!A1:A11000=A" & 17 + j & _ ")*(AGTHistory!C1:C11000=Main!B5),AGTHistory!K1:K1 1000)") Worksheets("main").Range("B18:C19").Value = val -- HTH Bob Phillips (remove nothere from email address if mailing direct) "ram" wrote in message ... Can someone show me how the results from the following code can be pasted all at the same time instead of pasting in one cell at a time. Dim J As Integer For J = 1 To 2 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)") Next J End Sub Thanks for all your help |
Paste
You are fussy <vbg
With Worksheets("main").Range("B18:C19") .Formula = _ "=SUMPRODUCT((AGTHistory!$A$1:$A$11000=$A18)*" & _ "(AGTHistory!$C$1:$C$11000=Main!$B$5),AGTHistory!K $1:K$11000)" .Value = .Value End With -- HTH Bob Phillips (remove nothere from email address if mailing direct) "ram" wrote in message ... Hi Bob, Your code is working however it it placing the formulas in the cells and i'm trying to only have the values entered. any suggestions Thanks in advance "Bob Phillips" wrote: How about this then Worksheets("main").Range("B18:C19").Formula = _ "=SUMPRODUCT((AGTHistory!$A$1:$A$11000=$A18)*" & _ "(AGTHistory!$C$1:$C$11000=Main!$B$5),AGTHistory!K $1:K$11000)" -- HTH Bob Phillips (remove nothere from email address if mailing direct) "ram" wrote in message ... Hi Bob, It returns the same number in all 4 cells and they should be differant. Date Sales Expenses Jan 1000 400 Feb 1500 300 Any suggestions Thanks Thanks "Bob Phillips" wrote: I think this should do it val = Evaluate("=SUMPRODUCT((AGTHistory!A1:A11000=A" & 17 + j & _ ")*(AGTHistory!C1:C11000=Main!B5),AGTHistory!K1:K1 1000)") Worksheets("main").Range("B18:C19").Value = val -- HTH Bob Phillips (remove nothere from email address if mailing direct) "ram" wrote in message ... Can someone show me how the results from the following code can be pasted all at the same time instead of pasting in one cell at a time. Dim J As Integer For J = 1 To 2 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)") Next J End Sub Thanks for all your help |
Paste
Bob,
Thanks for all your help this really helped me out. "Bob Phillips" wrote: You are fussy <vbg With Worksheets("main").Range("B18:C19") .Formula = _ "=SUMPRODUCT((AGTHistory!$A$1:$A$11000=$A18)*" & _ "(AGTHistory!$C$1:$C$11000=Main!$B$5),AGTHistory!K $1:K$11000)" .Value = .Value End With -- HTH Bob Phillips (remove nothere from email address if mailing direct) "ram" wrote in message ... Hi Bob, Your code is working however it it placing the formulas in the cells and i'm trying to only have the values entered. any suggestions Thanks in advance "Bob Phillips" wrote: How about this then Worksheets("main").Range("B18:C19").Formula = _ "=SUMPRODUCT((AGTHistory!$A$1:$A$11000=$A18)*" & _ "(AGTHistory!$C$1:$C$11000=Main!$B$5),AGTHistory!K $1:K$11000)" -- HTH Bob Phillips (remove nothere from email address if mailing direct) "ram" wrote in message ... Hi Bob, It returns the same number in all 4 cells and they should be differant. Date Sales Expenses Jan 1000 400 Feb 1500 300 Any suggestions Thanks Thanks "Bob Phillips" wrote: I think this should do it val = Evaluate("=SUMPRODUCT((AGTHistory!A1:A11000=A" & 17 + j & _ ")*(AGTHistory!C1:C11000=Main!B5),AGTHistory!K1:K1 1000)") Worksheets("main").Range("B18:C19").Value = val -- HTH Bob Phillips (remove nothere from email address if mailing direct) "ram" wrote in message ... Can someone show me how the results from the following code can be pasted all at the same time instead of pasting in one cell at a time. Dim J As Integer For J = 1 To 2 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)") Next J End Sub Thanks for all your help |
Paste
ram,
I must admit that the final solution doesn't seem much more efficient than your original. Was your code only an extract? -- HTH Bob Phillips (remove nothere from email address if mailing direct) "ram" wrote in message ... Bob, Thanks for all your help this really helped me out. "Bob Phillips" wrote: You are fussy <vbg With Worksheets("main").Range("B18:C19") .Formula = _ "=SUMPRODUCT((AGTHistory!$A$1:$A$11000=$A18)*" & _ "(AGTHistory!$C$1:$C$11000=Main!$B$5),AGTHistory!K $1:K$11000)" .Value = .Value End With -- HTH Bob Phillips (remove nothere from email address if mailing direct) "ram" wrote in message ... Hi Bob, Your code is working however it it placing the formulas in the cells and i'm trying to only have the values entered. any suggestions Thanks in advance "Bob Phillips" wrote: How about this then Worksheets("main").Range("B18:C19").Formula = _ "=SUMPRODUCT((AGTHistory!$A$1:$A$11000=$A18)*" & _ "(AGTHistory!$C$1:$C$11000=Main!$B$5),AGTHistory!K $1:K$11000)" -- HTH Bob Phillips (remove nothere from email address if mailing direct) "ram" wrote in message ... Hi Bob, It returns the same number in all 4 cells and they should be differant. Date Sales Expenses Jan 1000 400 Feb 1500 300 Any suggestions Thanks Thanks "Bob Phillips" wrote: I think this should do it val = Evaluate("=SUMPRODUCT((AGTHistory!A1:A11000=A" & 17 + j & _ ")*(AGTHistory!C1:C11000=Main!B5),AGTHistory!K1:K1 1000)") Worksheets("main").Range("B18:C19").Value = val -- HTH Bob Phillips (remove nothere from email address if mailing direct) "ram" wrote in message ... Can someone show me how the results from the following code can be pasted all at the same time instead of pasting in one cell at a time. Dim J As Integer For J = 1 To 2 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)") Next J End Sub Thanks for all your help |
Paste
Yes
"Bob Phillips" wrote: ram, I must admit that the final solution doesn't seem much more efficient than your original. Was your code only an extract? -- HTH Bob Phillips (remove nothere from email address if mailing direct) "ram" wrote in message ... Bob, Thanks for all your help this really helped me out. "Bob Phillips" wrote: You are fussy <vbg With Worksheets("main").Range("B18:C19") .Formula = _ "=SUMPRODUCT((AGTHistory!$A$1:$A$11000=$A18)*" & _ "(AGTHistory!$C$1:$C$11000=Main!$B$5),AGTHistory!K $1:K$11000)" .Value = .Value End With -- HTH Bob Phillips (remove nothere from email address if mailing direct) "ram" wrote in message ... Hi Bob, Your code is working however it it placing the formulas in the cells and i'm trying to only have the values entered. any suggestions Thanks in advance "Bob Phillips" wrote: How about this then Worksheets("main").Range("B18:C19").Formula = _ "=SUMPRODUCT((AGTHistory!$A$1:$A$11000=$A18)*" & _ "(AGTHistory!$C$1:$C$11000=Main!$B$5),AGTHistory!K $1:K$11000)" -- HTH Bob Phillips (remove nothere from email address if mailing direct) "ram" wrote in message ... Hi Bob, It returns the same number in all 4 cells and they should be differant. Date Sales Expenses Jan 1000 400 Feb 1500 300 Any suggestions Thanks Thanks "Bob Phillips" wrote: I think this should do it val = Evaluate("=SUMPRODUCT((AGTHistory!A1:A11000=A" & 17 + j & _ ")*(AGTHistory!C1:C11000=Main!B5),AGTHistory!K1:K1 1000)") Worksheets("main").Range("B18:C19").Value = val -- HTH Bob Phillips (remove nothere from email address if mailing direct) "ram" wrote in message ... Can someone show me how the results from the following code can be pasted all at the same time instead of pasting in one cell at a time. Dim J As Integer For J = 1 To 2 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)") Next J End Sub Thanks for all your help |
| All times are GMT +1. The time now is 02:58 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com