copying an array formula


M

Matt S

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
 
Ad

Advertisements

B

Barb Reinhardt

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")
 
Ad

Advertisements

M

Matt S

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top