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

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" wrote in message
ups.com...
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

Hi Mike,

Thanks for your reply.

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

Mike Fogleman wrote:
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" wrote in message
ups.com...
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

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.
"Len" wrote in message
oups.com...
Hi Mike,

Thanks for your reply.

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

Mike Fogleman wrote:
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" wrote in message
ups.com...
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

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" wrote in message
...
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.
"Len" wrote in message
oups.com...
Hi Mike,

Thanks for your reply.

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

Mike Fogleman wrote:
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" wrote in message
ups.com...
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

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




"Mike Fogleman" wrote in message
...
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" wrote in message
...
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.
"Len" wrote in message
oups.com...
Hi Mike,

Thanks for your reply.

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

Mike Fogleman wrote:
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" wrote in message
ups.com...
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