Auto Num

[Debbie Hurlbut]

I want to set up a column that "auto numbers and
increments by one" as the user tabs into the cell. It
should be very simple, but I can't remember how to do it.

 

[Dave Peterson]

You could do something like:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("a:a")) Is Nothing Then Exit Sub

On Error GoTo errHandler:

Application.EnableEvents = False
If Target.Row = 1 Then
If IsEmpty(Target) Then
Target.Value = 1
End If
Else
If IsEmpty(Target.Offset(-1, 0)) Then
'do nothing
Else
If IsNumeric(Target.Offset(-1, 0)) Then
Target.Value = Target.Offset(-1, 0).Value + 1
End If
End If
End If

errHandler:
Application.EnableEvents = True

End Sub

Right click on the worksheet tab that should have this behavior and select view
code. Paste this in.

Back to excel to test it.

I looked for a selected cell in column A and added one to the cell above's
value. (If it wasn't empty.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm