Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
copying an array formula
All,
I tried this code which contains my array formula: ActiveCell.Range("A1:A3").Copy Destination:=ActiveCell.Range("A1:J3,L1:O3,Q1:S3") Thinking it would copy the Formula Array but on execution, VBA promptly went to End Sub and stopped. So then I tried this: ActiveCell.Offset(3, 0).Range("A1:J1,L1:O1,Q1:S1").FormulaArray = "=MIN(IF((R13C:R" & LastRow & "C)0,R13C:R" & LastRow & "C))" It copies the formula over... but it retains the same column as the first cell for the range. It also only pastes into A1:J1 and ignores the rest. Please help! How do I copy this formula array in multiple cells? Thanks so much, Matt |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
copying an array formula
It's not working because you've got syntax errors in your code. I'm not
sure if this will do what you want, but try it. ActiveSheet.Range("A1:A3").Copy _ Destination:=ActiveSheet.Range("A1:J3,L1:O3,Q1:S3" ) -- HTH, Barb Reinhardt "Matt S" wrote: All, I tried this code which contains my array formula: ActiveCell.Range("A1:A3").Copy Destination:=ActiveCell.Range("A1:J3,L1:O3,Q1:S3") Thinking it would copy the Formula Array but on execution, VBA promptly went to End Sub and stopped. So then I tried this: ActiveCell.Offset(3, 0).Range("A1:J1,L1:O1,Q1:S1").FormulaArray = "=MIN(IF((R13C:R" & LastRow & "C)0,R13C:R" & LastRow & "C))" It copies the formula over... but it retains the same column as the first cell for the range. It also only pastes into A1:J1 and ignores the rest. Please help! How do I copy this formula array in multiple cells? Thanks so much, Matt |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
copying an array formula
Barb,
This is my code, following your suggestion: ActiveCell.Offset(3, 0).FormulaArray = "=MIN(IF((R13C:R" & LastRow & "C)0,R13C:R" & LastRow & "C))" ActiveCell.Range("A1:A3").Copy Destination:=ActiveCell.Range("A1:J3,L1:O3,Q1:S3") It evaluates the Formula Array but then when it tries to copy, instead it skips the rest of my code and goes straight to "End Sub". What is causing that? Thanks, Matt "Barb Reinhardt" wrote: It's not working because you've got syntax errors in your code. I'm not sure if this will do what you want, but try it. ActiveSheet.Range("A1:A3").Copy _ Destination:=ActiveSheet.Range("A1:J3,L1:O3,Q1:S3" ) -- HTH, Barb Reinhardt "Matt S" wrote: All, I tried this code which contains my array formula: ActiveCell.Range("A1:A3").Copy Destination:=ActiveCell.Range("A1:J3,L1:O3,Q1:S3") Thinking it would copy the Formula Array but on execution, VBA promptly went to End Sub and stopped. So then I tried this: ActiveCell.Offset(3, 0).Range("A1:J1,L1:O1,Q1:S1").FormulaArray = "=MIN(IF((R13C:R" & LastRow & "C)0,R13C:R" & LastRow & "C))" It copies the formula over... but it retains the same column as the first cell for the range. It also only pastes into A1:J1 and ignores the rest. Please help! How do I copy this formula array in multiple cells? Thanks so much, Matt |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array formula copying blanks as zeros | New Users to Excel | |||
Tricky array formula issue - Using array formula on one cell, then autofilling down a range | Excel Programming | |||
Copying an Array Formula | Excel Discussion (Misc queries) | |||
Copying array formula to a range of cells... | Excel Programming | |||
Copying an array formula... | Excel Worksheet Functions |