Drag formula down depending on number in particular cell [Closed]

Report
Posts
1
Registration date
Monday May 15, 2017
Status
Member
Last seen
May 15, 2017
-
Posts
2718
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
April 15, 2021
-
Hello,





I don't know if it is possible but here we go :)

I have data from columns A to O

In cell P1 there is a number and in P2 a formula.

Now I want to drag down the formula the same amount of cells than the number in P1.


For example:

P1 = 150

Than I need the formula dragged down from P2 to P 151.

Is this doable?

1 reply

Posts
2718
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
April 15, 2021
459
Hi Gerben,

The following code will do as requested:
Sub RunMe()
Range("P2").AutoFill Destination:=Range("P2:P" & Range("P1").Value + 1)
End Sub


Place the code in a standard module and run the code manually or create a shortcut for it (do this in the display macro screen Alt+F8, after choosing options).


Or when you want to run the code automatically when you confirm the entry in P1 then place the following code in the sheet module:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("P1")) Is Nothing Then Exit Sub
Range("P2").AutoFill Destination:=Range("P2:P" & Target.Value + 1)
End Sub


Best regards,
Trowa

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!