Drag formula down depending on number in particular cell

Gerben69 1 Posts Monday May 15, 2017Registration date May 15, 2017 Last seen - May 15, 2017 at 08:46 AM - Latest reply: TrowaD 2348 Posts Sunday September 12, 2010Registration dateModeratorStatus April 24, 2018 Last seen
- May 15, 2017 at 10:44 AM
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?
See more 

1 reply

Reply to this topic
TrowaD 2348 Posts Sunday September 12, 2010Registration dateModeratorStatus April 24, 2018 Last seen - May 15, 2017 at 10:44 AM
0
Helpful
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
Respond to TrowaD