Looping within same range

[aysel1989]

Hi guys, i did some basic looping with below codes but it didn't give me the numbers running as my attachment. What should i do to make the number running in below range every time i insert it?

sub test()
for x=0 to 9
cells(x+1,1)=x+1
next x
end sub

 

[aysel1989]

1
2
3
4
5
6
7
8
9
10
1
2
3
4
5
6
7
8
9
10

 

[Joe4]

I think this will do what you want:

Sub test()

    Dim lastRow As Long
    Dim x As Long

'   Find last populated row in column A
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    If lastRow = 1 And Range("A1") = "" Then lastRow = 0
    
    For x = 0 To 9
        Cells(lastRow + x + 1, 1) = x + 1
    Next x
    
End Sub

 

[Fluff]

How about

Sub test()
For x = 0 To 9
Range("A" & Rows.Count).End(xlUp).Offset(1) = x + 1
Next x
End Sub

 

[Rick Rothstein]

To start the first fill at cell A1...

[table="width: 500"]
[tr]
	[td]Sub InsertOneToTenAtBottomOfColumnA()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Cells(LastRow - (LastRow > 1), "A").Resize(10) = [{1;2;3;4;5;6;7;8;9;10}]
End Sub[/td]
[/tr]
[/table]

If you have a header in cell A1 (or don't mind if the first value starts in cell A2 even if cell A1 is blank), then the code can be simplified to this one-liner...

[table="width: 500"]
[tr]
	[td]Sub InsertOneToTenAtBottomOfColumnA()
  Cells(Rows.Count, "A").End(xlUp).Offset(1).Resize(10) = [{1;2;3;4;5;6;7;8;9;10}]
End Sub[/td]
[/tr]
[/table]

 

[Joe4]

Fluff,

That code works great (short and concise), as long as column A isn't totally blank. If it is, it will skip row 1 and first post to row 2.
That is why I added the lastRow calculation and did some extra checking on it.

If they have a title row in row 1, then it won't be an issue.

 

[Eric W]

Not entirely sure what you want, but maybe:

Sub test()
Dim x As Integer

    For x = 0 To 19
        Cells(x + 1, 1) = x Mod 10 + 1
    Next x

End Sub

 

[Joe4]

Not entirely sure what you want, but maybe:

I think they want it so that each time they run the Macro, it adds ten more rows from 1-10 under the last set of numbers.

 

[Eric W]

If so, then your macro, or one of the others, should work. My interpretation was having 1-10 repeat down an arbitrary length. If so, then mine should work.

 

[Fluff]

Fluff,

That code works great (short and concise), as long as column A isn't totally blank. If it is, it will skip row 1 and first post to row 2.
That is why I added the lastRow calculation and did some extra checking on it.

If they have a title row in row 1, then it won't be an issue.

Agreed, my impression (rightly or wrongly), was that the OP was more interested in how to get the results to print down every time , rather than overwrite the existing data