Modifying VB Code:Excell Macro and Visal Basic Programming

Hello All.

I am very new to using Excell Macros, infact just a few hours of use till now.I need a quick help on Macros.
I have a formula in cell B4, which I would normally drag all the way to another cell,B14 .So I created the macro , by pushing the record Macro option. Here it is below.

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("B4").Select
    Selection.AutoFill Destination:=Range("B4:B14"), Type:=xlFillDefault
    Range("B4:B14").Select
End Sub

Question : How can I change this script so that the Macro is executed from cell B4 to BX , the number X , would be decided by a value in cell B2. If the value is 10 ,
then the macro should execute from "Cell:B4" till "Cell:Address of B4 + 10"

Without knowing the actual formula in B4 I would opt for something along the lines of:

Sub Macro1()
Cells(4,"B").Resize(1+Cells(2,"B")).FormulaR1C1 = Cells(4,"B").FormulaR1C1
End Sub

Hello,

Thank you for your reply....

WoW!

That works... Thank you again..

Untill my next querry!
My Best Regards,
Aero777

re: the formula... INDEX should suffice and has the added advantage of being non-volatile unlike the existing formula

=INDEX(A:A,ROWS($A$1:$A$4)+2*(ROWS($A$4:A4)-1))

In code terms just apply the formula in one go (including B4 itself)

Public Sub Example()
Cells(4,"B").Resize(1+Cells(2,"B")).FormulaR1C1 = "=INDEX(C1,ROWS(R1C:R4C)+2*(ROWS(R4C:RC)-1))"
End Sub

Where B2 contains how many extra formulae are required in addition to that in B4.