| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Paste and Paste Special No Longer Working - Excel 2003 | Excel Discussion (Misc queries) | |||
| In Excel: add a Paste-Special Option to paste IN REVERSE ORDER. | Excel Worksheet Functions | |||
| Excel cut/Paste Problem: Year changes after data is copy and paste | Excel Discussion (Misc queries) | |||
| How do I capture user paste action and convert to Paste Special | Excel Programming | |||
| Macro to Paste to specific line, and continue to Paste each time on next row not over | Excel Programming | |||
Hybrid Mode
