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?