Copy FormulaArray

[Len]

Hi,

I've a problem on copying Formula Array from cell O2 to cell O14 or the
rest of cells in column O by using the code1 belows ( ie formula array
is not copied rather copy formula only) :-

Dim rng11 As Range
Set rng11 = Range(Cells(2, 6), Cells(2, 6).End(xlDown))
rng11.Offset(0, 9).Formula = Cells(2, 15).Formula

However, there is no problem if I use the code2 "Selection.AutoFill
Destination:=Range("O2:O14")to copy the formula array but only limit to
cell O14 only, particularly if there have more data to copy in the rest
of cells in column O. Thus, code1 will be able to handle this situation
if it can take formula array

Can anyone help me to solve the above problem ?

Thanks in advance

Regards
Len

 

[Mike Fogleman]

If you want an Array Formula then say "FormulaArray" in your code.

Dim rng11 As Range
Set rng11 = Range(Cells(2, 6), Cells(2, 6).End(xlDown))
rng11.Offset(0, 9).FormulaArray = Cells(2, 15).FormulaArray

Mike F

 

[Len]

Hi Mike,

Thanks for your reply.

Sorry, I've already tried but it didn't work
regards
Len

 

[Mike Fogleman]

It worked for me. I tested it on a clean sheet and copied the array formula
down column O as far as there was data in column G.

 

[Mike Fogleman]

I meant down column F, not G. When you say "It didn't work", What exactly is
"It" that doesn't work? The only thing I can imagine is that the formula is
copied down as exact copies, meaning any cell references would also be exact
copies, ignoring relative references.

 

[Mike Fogleman]

If you want the formula copied down with relative references, as in
"FillDown", then use this type of code....

Range("O2", Range("F2").End(xlDown)).Offset(0, 9).FillDown

 

[Len]

Hi Mike,

Thanks again

Sorry, my mistake, actually I need to copy down the formula array with
relative reference. Thus it is still not working if the code1 is
modified as below
Dim rng11 As Range
Set rng11 = Range(Cells(2, 6), Cells(2, 6).End(xlDown))
rng11.Offset(0, 9).FormulaArray = Cells(2, 15).FormulaArray.

How to fit your suggested code "Range("O2",
Range("F2").End(xlDown)).Offset(0, 9).FillDown " in the above code1 if
I were to use it ?

Regards
Len

 

[Len]

Hi Mike,

It's Ok, I managed to get the result with your suggested code which is
modified in code1.

Thanks alot

Regards
Len