Adding 1 in Every 15th Row

C

Calee_143

I am looking for a way to have a column filled with the same number fo
every 15 cells in the column and then add 1 in the 16th Cell t
continue the same number until 30 ....

For example A1-A15 = 150001; A16-A30 = 150002; A31-A46 150003

Thanks for any help you can provide me with.

Eric
 
P

Peo Sjoblom

Put this in a cell and copy down

=150000+CEILING(ROW(1:1)/15,1)

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
 
D

Dave Peterson

How about:

Option Explicit
Sub testme()

Dim iCtr As Long
Dim StartValue As Long
Dim HowManyTimes As Long
Dim RowsPerGroup As Long

StartValue = 150001
RowsPerGroup = 15
HowManyTimes = 30

For iCtr = 1 To HowManyTimes
ActiveSheet.Cells((iCtr - 1) * RowsPerGroup + 1, "A") _
.Resize(RowsPerGroup) _
= StartValue + (iCtr - 1)
Next iCtr

End Sub

Or your could use a formula:
Put this in A1 and drag down to row 450:

=150000+INT((ROW()-1)/15)+1
 
C

Calee_143

What would be the formula to keep the zero value at the beginning i.e
0150001 If using the formula =15000+CEILING(ROW(1:1)/5,1
 
C

Calee_143

What would be the formula to keep the zero value at the beginning i.e
0150001 If using the formula =15000+CEILING(ROW(1:1)/5,1
 
M

Myrna Larson

You can do that with a custom number format: 0000000

But the first number sould be 015001, not 0150001
 
Top