Find Max Alphanumeric Serial Number

[Pinkster69]

Hey Guys,

I am looking to find the Max Alphanumeric Serial Number that is in a column and increment by 1! I have code that finds the last "Sequential" serial number in the column and it increments by 1 but unfortunately all my Serial numbers are NOT Sequential in the column!
Can someone please look at my code to see what I have to add to find the Max Alphanumeric Serial Number from a list of NON Sequential Numbers and increment what is found by 1.

Many thanks



Example:
GR1020
GR0950
GR1080
GR1850
GR0760

Dim iRow As LongDim ws As Worksheet


    Set ws = Worksheets("Goods Return")
    
   
    'find last data row from database
    iRow = ws.Cells(Rows.Count, 3).End(xlUp).Row
 
    If ws.Range("C" & iRow).Value = "" Then
        GoodsReturnIDTB.Text = "GR1"
        
    Else
        GoodsReturnIDTB.Text = "GR" & Val(Mid(ws.Cells(iRow, 3).Value, 3)) + 1
       
    End If

 

[Momentman]

like

Excel 2010
	D
1	GR6789
Sheet3

Cell Formulas
Range	Formula
D1	{="GR"&MAX(MID(C1:C6,3,LEN(C1:C6)-2)*1)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[hiker95]

Pinkster69,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.

 

[Pinkster69]

Tried your code again! It worked!
Seems the Type Mismatch was due to my range which encompassed my heading!

Thanks again,

Have to say... U Da Man Too!!....

 

[Pinkster69]

Hey mikerickson!

Thanks for helping out, I got it sorted thanks to Hiker95 & Momentman,

I appreciate the effort alot

Cheers.