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

 

[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]

In addition to the above non-looping solutions, if you cannot be sure if cell A1 will be blank or not, then you can use this non-looping macro which will adapt itself to either possibility for cell A1...

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

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.

That was my impression as well.

 

[aysel1989]

Thanks fluff ! your codes working fine and exactly what i want to do in my excel ! but my number starts on column A5 and need to add ten more rows from 1-10 under the last set of numbers when click on my macros. do you have any ideas on how to solve this?

 

[Rick Rothstein]

Thanks fluff ! your codes working fine and exactly what i want to do in my excel ! but my number starts on column A5 and need to add ten more rows from 1-10 under the last set of numbers when click on my macros. do you have any ideas on how to solve this?

Do you have anything in cell A4?

 

[aysel1989]

Yes, cell A4 contains title..so basically the number need to run starts from A5. then add more rows from 1-10 under the last set of numbers

 

[Rick Rothstein]

Yes, cell A4 contains title..so basically the number need to run starts from A5. then add more rows from 1-10 under the last set of numbers

This code, which I posted earlier, will do what you want then...

[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]

 

[aysel1989]

Thanks Rick ! Your codes working well ! But i would prefer to insert the numbers from 1-10 first after i clicked on macro. I tested your codes and it generated all the number from 1-10 till end of excel sheet. How u do the looping if i clicked on macros, column A generate first number from 1-10 with the rest data on my column B. then i clicked macros again add another ten rows of data with number 1-10 been generated. I'm sorry if i'm asking a simple looping questions as i just get familiar with it less than a week time.

 

[Fluff]

The code that I supplied, along with the codes that Joe4 & Rick Rothstein supplied, will put the numbers 1 to 10 from A5 down, if you run the macro again you'll get a further 10 rows numbered from 1 to 10.
Is that not what you're after?