[Solved] Find maximum value in a range of strings

by


Use the next function, please:

Function newID(sh As Worksheet) As String
 Dim lastR As Long, strID As String, arr As Variant, i As Long

 lastR = sh.Range("A" & Rows.count).End(xlUp).Row
 arr = sh.Range("A2:A" & lastR).Value
 
 strID = left(sh.Range("A2").Value, 4)
 For i = 1 To UBound(arr)
    arr(i, 1) = CLng(Right(arr(i, 1), 3))
 Next i
 newID = strID & Format(WorksheetFunction.Max(arr) + 1, "000")
End Function

It can be called/used in this way:

Sub testNewID()
   Dim sh As Worksheet
   Set sh = ActiveSheet 'use here the necessary worksheet
   MsgBox newID(sh)
 End Sub

1

solved Find maximum value in a range of strings