| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find next ID in non sort column
Hi,
I have sheet with column A the values in the column a ss001 ss002 hh001 hh005 ss003 The values are ID. I want to find the next ID according to the prefix. In the above example the next ID when prefix 'ss' is '22004' and when prefix is 'hh' the next ID is 'hh006' How can I do it in the code ? (VBA+excel) How I find the next ID? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find next ID in non sort column
dim s as String, sNextID as string
Dim max as Long, n as String Dim lNum as Long max = 0 for each cell in selection s = left(cell,2) n = Right(cell,len(cell)-2) if s = "ss" then lNum = clng(n) if lNum max then max = lNum end if end if Next sNextID = "ss" & format(lNum+1,"000") -- Regards, Tom Ogilvy "Shlomit" wrote: Hi, I have sheet with column A the values in the column a ss001 ss002 hh001 hh005 ss003 The values are ID. I want to find the next ID according to the prefix. In the above example the next ID when prefix 'ss' is '22004' and when prefix is 'hh' the next ID is 'hh006' How can I do it in the code ? (VBA+excel) How I find the next ID? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to sort without including column headings in sort | Excel Discussion (Misc queries) | |||
| data, sort option is grayed. how to sort on a column? | Excel Discussion (Misc queries) | |||
| how do I find and sort unique values from a column | Excel Worksheet Functions | |||
| Can Excel find & sort one item in all the cells in a column? | New Users to Excel | |||
| How can I find and sort specific data within a column? | Excel Discussion (Misc queries) | |||
Linear Mode
