Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Incrementing numbers in a column
Can someone help me? I am looking for a formula or macro to increment
numbers in a column such that when a number is entered in an empty cell, the remaining numbers (in the column) are incremented by 1. In the examples below, the Before example represents a spreadsheet with numbers in certain cells in Column A. The After example represents placing a number in an empty cell (A3) and the remaining numbers in the column are incremented by 1. Before |COL A 1|1 2| 3| 4|2 5| 6|3 7| 8|4 9| 10|5 After |COL A 1|1 2| 3|2 4|3 5| 6|4 7| 8|5 9| 10|6 (I would like this formula/macro to be in effect for all of Column A.) Thanks In Advance! -- Rodman Veney |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Incrementing numbers in a column
In your Sheet Module paste in:
Private Sub Worksheet_Change(ByVal Target As Range) Dim Rng As Range If Application.Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub Application.EnableEvents = False lrow = Cells(Rows.Count, "A").End(xlUp).Row Set Rng = Range(Cells(Target.Row + 1, Target.Column), Cells(lrow, 1)) For Each cell In Rng If cell < "" Then cell.Value = cell.Value + 1 End If Next cell Application.EnableEvents = True End Sub "Rodman" wrote: Can someone help me? I am looking for a formula or macro to increment numbers in a column such that when a number is entered in an empty cell, the remaining numbers (in the column) are incremented by 1. In the examples below, the Before example represents a spreadsheet with numbers in certain cells in Column A. The After example represents placing a number in an empty cell (A3) and the remaining numbers in the column are incremented by 1. Before |COL A 1|1 2| 3| 4|2 5| 6|3 7| 8|4 9| 10|5 After |COL A 1|1 2| 3|2 4|3 5| 6|4 7| 8|5 9| 10|6 (I would like this formula/macro to be in effect for all of Column A.) Thanks In Advance! -- Rodman Veney |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Incrementing numbers in cells | Excel Worksheet Functions | |||
incrementing account numbers | Excel Discussion (Misc queries) | |||
Incrementing numbers | Excel Worksheet Functions | |||
Table of incrementing by one numbers | New Users to Excel | |||
Incrementing Cell Numbers | Excel Discussion (Misc queries) |