Insert Row + Formula Problem, please help!

[excelstep]

I have a drilldown list of numbers in cell B2 using data validation. Once user select number in cell B2, it will insert rows below the numbers. The codes I have used:

Sub Insertline()
Dim nRows As Long
Dim range("B2") As Long
range("B2").Select
nRows = range("B2").Value
ActiveCell.Offset(1, 0).Resize(nRows).EntireRow.Insert
End Sub

How do I insert formula in the new rows inserted below B2 since number of rows varies dependent on cell B2? Is there a formula like Do, Loop that can help? Or should I set variable?

Say I want a simple formula like this, I have this:

range("B3").Select
ActiveCell.Formula = range("B2") - (range("B2") - 1)
ActiveCell.Offset(1, 0).Select

But this doesnt work when I have user select different rows in b2.
Please help! Thank you very much!

 

[dine]

Hi There,

Please try using the below code..

Sub Insertline()
    Dim nRows As Long
    nRows = range("B2").Value
    range("B3:B" & nRows + 2).EntireRow.Insert Shift:=xlDown
    range("B3").Value = range("B2").Value + range("B2").Value - 1
    range("B3:B" & nRows + 2).FillDown
'ActiveCell.Offset(1, 0).Resize(nRows).EntireRow.Insert
End Sub

Hope it will work.

Cheers,
Dine

 

[luolovepi]

To copy a formula to next cell, can use:

Range("B2").Copy
Range("B2").Offset(1, 0).PasteSpecial xlPasteFormulas

Maybe you can use for loop to copy formulas for all the new rows??

lolo

 

[excelstep]

Thanks for your reply. However, how do I set a formula like I have my selected no of rows in B2, then I want B3 until the end to be filtered with numbers starting from 1?

4 <- I have selected "4" in B2
1
2
3
4

4 rows will be inserted with the numbering starting from 1 until the number of selection. Is there a formula, like If something that can help this?