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