Drag formula down depending on number in particular cell

Ask a question Gerben69 1Posts Monday May 15, 2017Registration date May 15, 2017 Last seen - Last answered on May 15, 2017 at 10:44 AM by TrowaD

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 
plus moins
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,
Leave a comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!