Hi,

I tried to do it in a different way (a very long one)

p = Range("b65536").End(xlUp).Row
Range("B65536").End(xlUp).Select
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaArray = "=SUM(IF(ISERROR(FIND(B" & p & ",$A$2:$A$" & i -
1 & ")),0,1))"
q = ActiveCell.Address

If q < "D2" Then
ActiveCell.Copy
Range("D2").Select
ActiveSheet.Paste
If q < "D3" Then
ActiveCell.Offset(1, 0).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
End If
End If

Im getting an error - Run time error '1004' You cannot change part of an
array.

And the yellow debug line which gets highlighted is the second instance of
activesheet.paste above.

What I understand from above is that if there is an array formula at a cell
then we cannot paste an array formula over it.(Am I right?)

So even this long route has not helped me.

Please suggest a way if possible.

--
Thanks a lot,
Hari
India
"Hari" wrote in message
...
Hi,

In column D and row number p , where p = Range("b65536").End(xlUp).Row, I
have a ARRAYFORMULA.

Presently the VBA control is in the above specified cell.

I want to copy this formula in to all cells in the column D starting from
row 2 to row p-1.

So, I wrote the following code, (using macro recorder)

If ActiveCell.Address < D2 Then
Selection.Copy
ActiveCell.Offset(-1, 0).Range("A1").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveCell.Offset(-6, 0).Range("A1:A7").Select
ActiveCell.Activate
ActiveSheet.Paste

Problem is the in the above case p, the row number was equal to 9, hence
while recording I got the pasting only in D2:D8. How do I make the range
reference dynamic.

I tried with the relative referencing off but again this is also useless
to
me.

Selection.Copy
Range("D8").Select
Range(Selection, Selection.End(xlUp)).Select
Range("D2:D8").Select
Range("D8").Activate
ActiveSheet.Paste

Please tell me the correct syntax wherby I could copy the formula in
column
D, row P to all the cells above row P except cell D1.

--
Thanks a lot,
Hari
India