How to (elegantly) pass only the numeric portion of a String in a Range into a maximum finding funct-CodePudding

Have written a succesful subroutine for data looking like:

(The list will continually be added to and length will change)

Public Function GetNewID() As Long 
    GetNewID = 1   WorksheetFunction.Max(shList.Range("A2").CurrentRegion.Columns(1))
End Function

However, I really need data in the cells to always display the "ID-" text in front of the number (the additional characters are an important interface in multiple other areas of the broader program) and so need to parse out the "ID-" characters and determine the max of the remaining numbers. Fortunately the "ID-" will always be the same, 3 characters.

Tried the worksheetFunction.replace to replace "ID-" with "", and it worked, but it visibly replaces the data in the cells (want to maintain the visible text as "ID-XXX", there's some listboxes and similar its called into).

I guess I could write a loop and use LEFT/RIGHT to scrub all the values of their "ID-" and store those in a temporary array (one that's not displayed) and then use a max funtion on that, but there's got to be a more elegant way...

CodePudding user response：

I guess I could write a loop and use LEFT/RIGHT to scrub all the values of their "ID-" and store those in a temporary array (one that's not displayed) and then use a max funtion on that, but there's got to be a more elegant way...

There is... Use an array formula.

=MAX(VALUE(SUBSTITUTE(A1:A13,"ID-","")))

Change the range as applicable. Instead of pressing Enter, press Ctl Shift Enter

For VBA you can use

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    
    '~~> Change this to the relevant worksheet
    Set ws = Sheet1
    
    Dim lRow As Long
    
    With ws
        '~~> Find the last row
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row
        
        Debug.Print Evaluate("=MAX(VALUE(SUBSTITUTE(A2:A" & lRow & ",""ID-"","""")))")
    End With
End Sub

Output