repeating one number

[CORAL]

hi i want that in one column we have 24 of 1 then 24 of 2 then 24 of 3 up to 31 could you please send me the code

[Admin]

In C1: 24
In C2: 31

In C3 and copied down

=if(rows(c$3:c3)<=$c$2,$c$1&" of "&rows(c$3:c3),"")

is it what you are looking for ?

[azlinzi]

Or, you can put the following line in C3 and copy down:
=IF(ROWS(C$3:C3)<$C$1*$C$2,INT(ROWS(C$3:C3)/24+1),"")

[Rick Rothstein]

Or, in code, as requested...

Sub InsertThirtyOneSetsOfTwentyFourEach()
Dim X As Long, Z As Long, Index As Long, Nums(1 To 744, 1 To 1)
Const StartCell As String = "A1"
For X = 1 To 31
For Z = 1 To 24
Index = Index + 1
Nums(Index, 1) = X
Next
Next
Range(StartCell).Resize(744) = Nums
End Sub


Note: Adjust the starting cell address (as shown in red above) to match your actual requirements.

[snb]

or


Sub M_snb()
[A1].Resize(24 * 31).Name = "snb_002"
[Snb_002] = [index(int((row(snb_002)-1)/24)+1,)]
End Sub

[azumi]

Assumed your data on A1 and down, put this on B1 and copied down:

=A1&" Of "&COUNTIF($A$1:A1,A1)

[Scott Huish]

Sub Insert24x31()
With Range("A1:A744")
.Formula = "=INT((ROW()-1)/24)+1"
.Value = .Value
End With
End Sub