Find next ID in non sort column

[Guest]

Hi,
I have sheet with column A the values in the column are:
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?

 

[Guest]

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")