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

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

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