Consecutive numbering

[Francis Hookam]

Consecutive numbering

(Excel: mac 2001 but will also have to run on PC 2000)

Entries in C2 (starting at a specifiable row, in this instance R11) are
numbered consecutively (1, 2, 3, 4, etc) in C1

Traditionally I generate the numbers manually by typing 1 in R11C1 and 2 in
R12C1 and then selecting both and dragging down as far as necessary - ok, it
may show my age (Multiplan and all that), but there must be a slicker way

=1+R[-1]C is no good because I have to insert/remove rows from time to time
and so need a macro to quickly renumber C1, first finding out how far down
C2 extends

Thanks for any suggestions

Francis

 

[Bob Greenblatt]

Consecutive numbering

(Excel: mac 2001 but will also have to run on PC 2000)

Entries in C2 (starting at a specifiable row, in this instance R11) are
numbered consecutively (1, 2, 3, 4, etc) in C1

Traditionally I generate the numbers manually by typing 1 in R11C1 and 2 in
R12C1 and then selecting both and dragging down as far as necessary - ok, it
may show my age (Multiplan and all that), but there must be a slicker way

=1+R[-1]C is no good because I have to insert/remove rows from time to time
and so need a macro to quickly renumber C1, first finding out how far down
C2 extends

Thanks for any suggestions

Francis

Entering the first few numbers in a series, and then filling down is pretty
straight forward, and "slick" in my opinion. Why is this a problem? You
could use Edit-fill-Series if you don't feel like dragging. A macro to do
this is pretty simple, but you'd need to select it from the macros menu, or
hit a key to initiate it, which is not any "slicker" in my opinion.

 

[JE McGimpsey]

Entries in C2 (starting at a specifiable row, in this instance R11) are
numbered consecutively (1, 2, 3, 4, etc) in C1

Traditionally I generate the numbers manually by typing 1 in R11C1 and 2 in
R12C1 and then selecting both and dragging down as far as necessary - ok, it
may show my age (Multiplan and all that), but there must be a slicker way

=1+R[-1]C is no good because I have to insert/remove rows from time to time
and so need a macro to quickly renumber C1, first finding out how far down
C2 extends

I agree with Bob that fill down is pretty slick. Having said that,
however, I have a separate testing toolbar that I use to generate a
range of lists (sequential by 1s, 2,s 5s, 10s, etc., random integers,
random decimals, SSNs, mixed Alphanumeric, etc.). The specific macros I
use wouldn't be of much interest, but you could attach a macro like this
to a toolbar button (or, as Bob said, to a keyboard shortcut) to fill
the first column of the selection:

Public Sub FillFirstColumn()
Dim vArr As Variant
Dim i As Long
With Selection
ReDim vArr(1 To .Rows.Count)
For i = 1 To UBound(vArr)
vArr(i) = i
Next i
.Columns(1).Value = Application.Transpose(vArr)
End With
End Sub

 

[Willis Eschenbach]

I use a function I wrote called "numberabove":

Function numberabove(r)
Application.Volatile
Set here = r.Offset(-1, 0)
While Application.IsNumber(here.Value) = False And here.Row > 1
Set here = here.Offset(-1, 0)
Wend
numberabove = here.Value
End Function

This finds the first number above the range "r". To use it in a list, say I
want the next number in cell "C11". In cell C11 I put the formula

= numberabove(c11)+1

w.

---====---

Entries in C2 (starting at a specifiable row, in this instance R11) are
numbered consecutively (1, 2, 3, 4, etc) in C1

Traditionally I generate the numbers manually by typing 1 in R11C1 and 2 in
R12C1 and then selecting both and dragging down as far as necessary - ok, it
may show my age (Multiplan and all that), but there must be a slicker way

=1+R[-1]C is no good because I have to insert/remove rows from time to time
and so need a macro to quickly renumber C1, first finding out how far down
C2 extends