Drag formula down depending on number in particular cellClosed

Question

Hello, 



System Configuration: Windows / Chrome 57.0.2987.133

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?

TrowaD · Answer

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

Drag formula down depending on number in particular cell

1 response

Similar discussions

Subscribe To Our Newsletter!