Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to autonumber cells
I have an excel sheet where I want to be able to select a range of cells in a
column and have excel automatically number them based on the value in a previous row. e.g. if A1 = 1, and I select A2 through A4, I want to be able to automatically number A2 to A4 as 1.1, 1.2, 1.3. The idea is that if I then insert a new row between A2 and A3, I could select the new A2 through A5 and renumber as 1.1 through 1.4. I hope Ive made it clear what I am after. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to autonumber cells
Jim
one way: Sub AutoNumberCells() Dim iCount As Integer Dim BaseNumber As String Dim cell As Range iCount = 1 BaseNumber = Selection(1).Offset(-1, 0) For Each cell In Selection cell.Value = BaseNumber & "." & iCount iCount = iCount + 1 Next 'cell End Sub Regards Trevor "JimG" wrote in message ... I have an excel sheet where I want to be able to select a range of cells in a column and have excel automatically number them based on the value in a previous row. e.g. if A1 = 1, and I select A2 through A4, I want to be able to automatically number A2 to A4 as 1.1, 1.2, 1.3. The idea is that if I then insert a new row between A2 and A3, I could select the new A2 through A5 and renumber as 1.1 through 1.4. I hope I've made it clear what I am after. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to autonumber cells
The following appears to work for me, however, I also select the row that I
want to use as the number the following rows are based on. In your example, just select A1 through A4 instead of A2 through A4. Sub RenumRow() Dim i As Integer, j As Integer Dim cell As Range j = 0 For Each cell In Selection If Selection.Range("a1").Row = cell.Row Then i = cell.Value Else j = j + 1 cell.Value = i & "." & j End If Next cell End Sub "JimG" wrote: I have an excel sheet where I want to be able to select a range of cells in a column and have excel automatically number them based on the value in a previous row. e.g. if A1 = 1, and I select A2 through A4, I want to be able to automatically number A2 to A4 as 1.1, 1.2, 1.3. The idea is that if I then insert a new row between A2 and A3, I could select the new A2 through A5 and renumber as 1.1 through 1.4. I hope Ive made it clear what I am after. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Drag cells autonumber feature went bye bye?!?!? | Excel Discussion (Misc queries) | |||
Can Excel AutoNumber | Excel Discussion (Misc queries) | |||
Autonumber from Form | New Users to Excel | |||
autonumber | Excel Discussion (Misc queries) | |||
Question on autonumber | Excel Programming |