Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ID numbers
I have a sheet containg rows of data, column A has an ID
number. For example - 1, 2, 3, 4, 5, 6, 7, 8, 9, 10. Users amend the ID number and click a button which sorts the sheet in ID ascending order. The user may want to amalgamate 1 and 7 so he will change the 1 to a 7 and click the sort button: Before clicking 7, 2, 3, 4, 5, 6, 7, 8, 9, 10 After clicking 2, 3, 4, 5, 6, 7, 7, 8, 9, 10 What I want to add on to the end of the sort macro is a bit of code that will renumber the ID's so that they make sense, that is, start at 1 and don't have any gaps. Is this possible? Thanks in advance. Gareth |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ID numbers
set rng = Range(cells(1,1),Cells(rows.count,1).End(xlup))
i = 0 for each cell in rng i = i + 1 cell.Value = i Next or you could use the fill method Sub Renumber() Set Rng = Range(Cells(1, 1), Cells(1, 1).End(xlDown)) Cells(1, 1).Value = 1 Rng.DataSeries Rowcol:=xlColumns, _ Type:=xlLinear, Date:=xlDay, _ Step:=1, Trend:=False End Sub -- Regards, Tom Ogilvy "Gareth" wrote in message ... I have a sheet containg rows of data, column A has an ID number. For example - 1, 2, 3, 4, 5, 6, 7, 8, 9, 10. Users amend the ID number and click a button which sorts the sheet in ID ascending order. The user may want to amalgamate 1 and 7 so he will change the 1 to a 7 and click the sort button: Before clicking 7, 2, 3, 4, 5, 6, 7, 8, 9, 10 After clicking 2, 3, 4, 5, 6, 7, 7, 8, 9, 10 What I want to add on to the end of the sort macro is a bit of code that will renumber the ID's so that they make sense, that is, start at 1 and don't have any gaps. Is this possible? Thanks in advance. Gareth |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ID numbers
lrow = ActiveSheet.UsedRange.Row - 1 +
ActiveSheet.UsedRange.Rows.Count For x = 1 To lrow ActiveSheet.Cells(x, 1).Value = x Next x How's that? - Piku -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ID numbers
perhaps I didn't make it clear exactly what I'm after.
start ID's: 1,2,3,4,5,6,7,8,9,10 user changes 1 to a 4 and sorts, gets this: 2,3,4,4,5,6,7,8,9,10 I want to resort the ID's to end up with: 1,2,3,3,4,5,6,7,8,9 Or start ID's: 1,2,3,4,5,6,7,8,9,10 user changes 4 to 9 and sorts, gets this: 1,2,3,5,6,7,8,9,9,10 I want to resort the ID's to end up with: 1,2,3,4,5,6,7,8,9,9 Hope you can help. -----Original Message----- set rng = Range(cells(1,1),Cells(rows.count,1).End(xlup)) i = 0 for each cell in rng i = i + 1 cell.Value = i Next or you could use the fill method Sub Renumber() Set Rng = Range(Cells(1, 1), Cells(1, 1).End(xlDown)) Cells(1, 1).Value = 1 Rng.DataSeries Rowcol:=xlColumns, _ Type:=xlLinear, Date:=xlDay, _ Step:=1, Trend:=False End Sub -- Regards, Tom Ogilvy "Gareth" wrote in message ... I have a sheet containg rows of data, column A has an ID number. For example - 1, 2, 3, 4, 5, 6, 7, 8, 9, 10. Users amend the ID number and click a button which sorts the sheet in ID ascending order. The user may want to amalgamate 1 and 7 so he will change the 1 to a 7 and click the sort button: Before clicking 7, 2, 3, 4, 5, 6, 7, 8, 9, 10 After clicking 2, 3, 4, 5, 6, 7, 7, 8, 9, 10 What I want to add on to the end of the sort macro is a bit of code that will renumber the ID's so that they make sense, that is, start at 1 and don't have any gaps. Is this possible? Thanks in advance. Gareth . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
ID numbers
perhaps I didn't make it clear exactly what I'm after.
No you didn't. Try this: Sub Renumber1() Dim lastVal As Long Dim rng As Range, i As Long lastVal = -1 Set rng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp)) i = 0 For Each cell In rng If cell.Value < lastVal Then _ i = i + 1 lastVal = cell.Value cell.Value = i Next End Sub -- Regards, Tom Ogilvy "Gareth" wrote in message ... perhaps I didn't make it clear exactly what I'm after. start ID's: 1,2,3,4,5,6,7,8,9,10 user changes 1 to a 4 and sorts, gets this: 2,3,4,4,5,6,7,8,9,10 I want to resort the ID's to end up with: 1,2,3,3,4,5,6,7,8,9 Or start ID's: 1,2,3,4,5,6,7,8,9,10 user changes 4 to 9 and sorts, gets this: 1,2,3,5,6,7,8,9,9,10 I want to resort the ID's to end up with: 1,2,3,4,5,6,7,8,9,9 Hope you can help. -----Original Message----- set rng = Range(cells(1,1),Cells(rows.count,1).End(xlup)) i = 0 for each cell in rng i = i + 1 cell.Value = i Next or you could use the fill method Sub Renumber() Set Rng = Range(Cells(1, 1), Cells(1, 1).End(xlDown)) Cells(1, 1).Value = 1 Rng.DataSeries Rowcol:=xlColumns, _ Type:=xlLinear, Date:=xlDay, _ Step:=1, Trend:=False End Sub -- Regards, Tom Ogilvy "Gareth" wrote in message ... I have a sheet containg rows of data, column A has an ID number. For example - 1, 2, 3, 4, 5, 6, 7, 8, 9, 10. Users amend the ID number and click a button which sorts the sheet in ID ascending order. The user may want to amalgamate 1 and 7 so he will change the 1 to a 7 and click the sort button: Before clicking 7, 2, 3, 4, 5, 6, 7, 8, 9, 10 After clicking 2, 3, 4, 5, 6, 7, 7, 8, 9, 10 What I want to add on to the end of the sort macro is a bit of code that will renumber the ID's so that they make sense, that is, start at 1 and don't have any gaps. Is this possible? Thanks in advance. Gareth . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel change a group of positive numbers to negitive numbers | Excel Discussion (Misc queries) | |||
Decimal Numbers typed into Excel 2003 read as whole numbers | Excel Discussion (Misc queries) | |||
VLOOKUP should compare numbers stored as text to plain numbers. | Excel Worksheet Functions | |||
Help! How do you get excel to find the x(changes daily, marked in a cell from another formula) highest numbers in a group of numbers and sum them up? | Excel Worksheet Functions | |||
Help! How do you get excel to find the x(changes daily, marked in a cell from another formula) highest numbers in a group of numbers and sum them up? | Excel Worksheet Functions |