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]

I made the following assumptions.

1. That all codes start with GR
2. That the list is in column A

I used a msgbox to output the result, if it gives you the desired result, we can change as necessary

Sub newmacro2()    ReDim arr(Range("A" & Rows.Count).End(xlUp).Row) As Variant
    For I = 1 To Range("A" & Rows.Count).End(xlUp).Row
        arr(I) = 1 * Mid(Range("A" & I).Value, 3, Len(Range("A" & I).Value) - 2)
    Next I
    
    MsgBox Application.WorksheetFunction.Max(arr)
    
End Sub

 

[Pinkster69]

Hi Momentman, Thanks for the reply!

I tried your code but unfortunately it came up with an error that Variable "I" is not defined?
Most of your assumptions were correct except that the Data is in Column "C" Not in Column "A"

 

[Momentman]

Do you have option explicit at the top of your code? If yes maybe lets modify the code this way (i have also included the data as being in column C)

Sub newmacro2()
    Dim I As Integer
    ReDim arr(Range("C" & Rows.Count).End(xlUp).Row) As Variant
    For I = 1 To Range("C" & Rows.Count).End(xlUp).Row
        arr(I) = 1 * Mid(Range("C" & I).Value, 3, Len(Range("C" & I).Value) - 2)
    Next I
    
    MsgBox Application.WorksheetFunction.Max(arr)
    
End Sub

 

[Pinkster69]

I Have Option Explicit at the top of the page but unfortunately I am still getting an error "Type Mismatch" on this line of code...

arr(I) = 1 * Mid(Range("C" & I).Value, 3, Len(Range("C" & I).Value) - 2)

 

[hiker95]

Pinkster69,

The below macro is based on the text strings that you posted.

Sample raw data:

Excel 2007
	C
1	GR1020
2	GR0950
3	GR1080
4	GR1850
5	GR0760
6	GR1851
7
8
9
Sheet1

After the macro:

Excel 2007
	C
1	GR1020
2	GR0950
3	GR1080
4	GR1850
5	GR0760
6	GR1851
7	GR1852
8
9
Sheet1

And, if we ran the macro again:

Excel 2007
	C
1	GR1020
2	GR0950
3	GR1080
4	GR1850
5	GR0760
6	GR1851
7	GR1852
8	GR1853
9
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub FindMaxNbr()
' hiker95, 01/09/2014, ME749540
Dim lr As Long, c As Range, maxn As Long
lr = Cells(Rows.Count, 3).End(xlUp).Row
For Each c In Range("C1:C" & lr)
  If CLng(Right(Trim(c), 4)) > maxn Then
    maxn = CLng(Right(Trim(c), 4))
  End If
Next c
Range("C" & lr + 1) = "GR" & maxn + 1
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the FindMaxNbr macro.

 

[Momentman]

Give hiker95's macro a try. I ran the code i provided on the test data and it works.....

 

[mikerickson]

Try the CSE formula
=MAX((MID(A1:A100,3,255)&".00")+0)

 

[hiker95]

mikerickson, nicely done - one for my archives - thanks.

Pinkster69,

Sample raw data:

Excel 2007
	C
1	GR1020
2	GR0950
3	GR1080
4	GR1850
5	GR0760
6
7
Sheet1

If you put a slight variation of mikerickson's array formula in cell C6:
="GR" &MAX((MID($C$1:$C5,3,255)&".00")+0)+1


We get this:

Excel 2007
	C
1	GR1020
2	GR0950
3	GR1080
4	GR1850
5	GR0760
6	GR1851
7
Sheet1

Cell Formulas
Range	Formula
C6	{="GR" &MAX((MID($C$1:$C5,3,255)&".00")+0)+1}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Pinkster69]

Hey Hiker95, Thanks for the help!

Yip worked a treat!

U DA MAN