I have a list of numbers, let's say 1-100, and I would like each number to
list 8 times before the next number starts. So, lines 1-8 would be 1, lines
9-16 would be 2 and so on...

The following subroutine will do what you want.
Be aware it will overwrite non-blank cells
Practice on a blank worksheet; then copy to the 'working' worksheet!
Need help with VBA? See David McRitchie's site on "getting started" with VBA
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Sub repeater()
Dim myValue As Integer, myLast As Integer
Dim n As Integer, j As Integer
Dim Message, Title, Default

Message = "What is the last number?"
Title = "Last number"
Default = "1"
myLast = InputBox(Message, Title, Default)

Message = "How many repeats?"
Title = "Repeats" ' Set title.
Default = "1"
myValue = InputBox(Message, Title, Default)

Range("A1").Select ' where the first number goes

For n = 1 To myLast ' use For n = 5 to start at 5, etc
For j = 1 To myValue
ActiveCell.Value = n
ActiveCell.Offset(1, 0).Activate
Next j
Next n
End Sub


best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Natalie B" <Natalie wrote in message
...
I have a list of numbers, let's say 1-100, and I would like each number to
list 8 times before the next number starts. So, lines 1-8 would be 1,
lines
9-16 would be 2 and so on...

Did you get a chance to look at my consolidation problem?

Tyro

"Bernard Liengme" wrote in message
...
The following subroutine will do what you want.
Be aware it will overwrite non-blank cells
Practice on a blank worksheet; then copy to the 'working' worksheet!
Need help with VBA? See David McRitchie's site on "getting started" with
VBA
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Sub repeater()
Dim myValue As Integer, myLast As Integer
Dim n As Integer, j As Integer
Dim Message, Title, Default

Message = "What is the last number?"
Title = "Last number"
Default = "1"
myLast = InputBox(Message, Title, Default)

Message = "How many repeats?"
Title = "Repeats" ' Set title.
Default = "1"
myValue = InputBox(Message, Title, Default)

Range("A1").Select ' where the first number goes

For n = 1 To myLast ' use For n = 5 to start at 5, etc
For j = 1 To myValue
ActiveCell.Value = n
ActiveCell.Offset(1, 0).Activate
Next j
Next n
End Sub


best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Natalie B" <Natalie wrote in message
...
I have a list of numbers, let's say 1-100, and I would like each number to
list 8 times before the next number starts. So, lines 1-8 would be 1,
lines
9-16 would be 2 and so on...

Another way:

Enter the starting number in the first cell:

A1 = 1

Then enter this formula in the next cell (A2 in this case) and copy down as
needed:

=A$1+INT(ROWS(A$2:A2)/8)

--
Biff
Microsoft Excel MVP


"Natalie B" <Natalie wrote in message
...
I have a list of numbers, let's say 1-100, and I would like each number to
list 8 times before the next number starts. So, lines 1-8 would be 1,
lines
9-16 would be 2 and so on...