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

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


Mike Fogleman wrote:
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

Hi Mike,

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

Thanks alot

Regards
Len

Len wrote:
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


Mike Fogleman wrote:
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