i want to search a column for the largest number...but the numbers have an E
before them....

E05001
E05002
E05003
..
..
..
E05xxx

is it possible to find the largest one, then add one to it and give me back
that number? when the new year comes, the series will look like this:

E06001
E06002
..
..
..
E06xxx

Thanks for any input on this

With the 'number' of interest in E5 use the formula
="E"&TEXT(RIGHT(E5,LEN(E5)-1)+1,"00000")

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
i want to search a column for the largest number...but the numbers have an E
before them....

E05001
E05002
E05003
.
.
.
E05xxx

is it possible to find the largest one, then add one to it and give me back
that number? when the new year comes, the series will look like this:

E06001
E06002
.
.
.
E06xxx

Thanks for any input on this

steve:

if A1=Caption,A2=E05001,A3=E05002.....

you cat get largest one for Array Function

{=MAX(VALUE(RIGHT(A2:A17,5)))}

VBA Code

'''''''''''''''''''''''''''''''''''''''''''
Sub AddItem()
Dim r As String, rmax As String
r = Range("A65536").End(xlUp).Row
rmax = Application.Evaluate("MAX(VALUE(RIGHT(A2:A" & r & ",5)))")
Cells(r + 1, 1) = "E" & Format(rmax + 1, "00000")
End Sub

'''''''''''''''''''''''''''''''''''''''''''''

File DownLond:
http://vba.holyou.net/file/9412221.xls

--
天行å�¥ï¼Œå�›å*�以自強ä¸�æ�¯
地勢å�¤ï¼Œå�›å*�以厚德載物

http://www.vba.com.tw/plog/


"steve" wrote:

i want to search a column for the largest number...but the numbers have an E
before them....

E05001
E05002
E05003
.
.
.
E05xxx

is it possible to find the largest one, then add one to it and give me back
that number? when the new year comes, the series will look like this:

E06001
E06002
.
.
.
E06xxx

Thanks for any input on this