Macro to automatically number rows based on a number

[sean_nyc]

Hi there,

I am new to the group and was curious if anyone can help me out with a macro I need to create. I am trying to number rows based on a number in the header. However, I want to repeat a number a certain number of times until it moves on to the next number.

To provide more detail:
Number of rows I want to have the same number for: 2
A1: 5

Expected result:
Row 2: 1
Row 3: 1
Row 4: 2
Row 5: 2
Row 6: 3
Row 7: 3
Row 8: 4
Row 9: 4
Row 10: 5
Row 11: 5

This will then need to be extrapolated to be repeated for 45 rows.

Thank you!

 

[Snakehips]

sean_nyc,

Welcome to MrExcel.

Not sure if I'm understanding the 'repeated for 45 rows' ????

Try something like....

Sub NumberRows()
m = 45 'last row *******??????
c = 2 'same number
n = 0  
For r = 2 To m Step c
n = n + 1
Cells(r, 1) = n
Cells(r, 1).Offset(1, 0) = n
If n = Cells(1, 1) Then n = 0
Next r
End Sub

Hope that helps.

 

[Marcelo Branco]

Welcome to Mr Excel

You can achieve this using a formula.

Something like this

	A​	B​	C​
1​	5​		Repeat​
2​	1​		2​
3​	1​
4​	2​
5​	2​
6​	3​
7​	3​
8​	4​
9​	4​
10​	5​
11​	5​
12​

Formula in A2 copied down for how many rows you need

=IF($A$1*$C$2>=ROWS(A$2:A2),INT((ROWS(A$2:A2)+$C$2-1)/$C$2),"")

Hope this helps

M.

 

[CharlesChuckieCharles]

or this formula

in C3
=IF(C2=C1,C2+1,C2)

 

[sean_nyc]

@ Marcelo Branco: Thank you so much! This is exactly what I needed! Will definitely archive this formula for future use!

 

[Marcelo Branco]

@ Marcelo Branco: Thank you so much! This is exactly what I needed! Will definitely archive this formula for future use!

You are very welcome. Thanks for the feedback.

M.