PDA

View Full Version : autofill rows according to the integer in another cell.



janarocky
12-20-2012, 09:00 AM
Hi All,

I am new comer in this forum. Hope you will help me!

example
rows 10
aaa0
aaa1
aaa2
aaa3
aaa4
aaa5
aaa6
aaa7
aaa8
aaa9
the number i type in cell A1 should determine the range for autofill. i need coding for that.

hope u can understand what im trying to ask.

thankz for the help.

omp001
12-20-2012, 10:26 AM
1st option - solution by formula
put this formula into A2 and drag down
=IF(ROW()-2<$A$1,"aaa"&ROW()-2,"")
2nd option - solution by macro
put this code into the worksheet module; the code will fire by inserting a number in A1
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
Dim k As Long
Range([A2], [A2].End(xlDown)).ClearContents
k = 2
Do While k - 2 < [A1]
Cells(k, 1).Value = "aaa" & k - 2
k = k + 1
Loop
End Sub