Excel VBA - how to find the largest substring value in a column

Question

I have a column in a spreadsheet. The format of the data in each cell is aa-0001-xx. I need to examine the whole column to find the highest value of the sequence number. this would be the substring from column4 thru column7. I can find the sequence number using Mid(ActiveWorkbook.Sheets("Sheet1").Range("B2:B2"), 4, 4) But I need to find the max sequence in the whole column. I am doing this in VBA. Any help would be appreciated.

Here is my code so far: Private Sub CommandButton1_Click()

Dim sQuoteNumber As String
Dim sFileName As String
Dim sPathName As String
Dim checkit As String

'Log the Quote

'First, open the log file and determine the next sequential log number.

sPathName = "C:\Users\Rich\Documents\Bryan\BigProject\"
sFileName = "QuoteLog2016.xlsx"
ControlFile = ActiveWorkbook.Name

Workbooks.Open Filename:=sPathName & sFileName


'Create the new Quote Number
checkit = Mid(ActiveWorkbook.Sheets("Sheet1").Range("B2:B2"), 4, 4) ' This is a temp test line

 If Mid(ActiveWorkbook.Sheets("Sheet1").Range("B2:B2"), 4, 4) = "" Then
    sQuoteNumber = "16-0001"
Else
    'find the biggest number

    'Here I was looking to like pass the mid function to a Max function  of some sort.

    sQuoteNumber = "16-0002"
End If

MsgBox ("The new Quote Number is: " + sQuoteNumber)


'Save the log entry

Workbooks(sFileName).Close

The obvious solution is to loop over the column, extracting the sequence numbers, and keeping track of the largest one encountered, as well as the row in which it occurs. — John Coleman
– John Coleman, Commented Dec 18, 2015 at 14:59
Alternatively to John's solution you could also write an array that contains two columns with the first being the row number and the second being the substring. Then sort the array in descending order and your first entry is the largest value. — Luuklag
– Luuklag, Commented Dec 18, 2015 at 15:34
Or Copy and paste everything into a new column, use text to columns delimited on "-" then sort on the second column descending and you will get your highest. — Scott Craner
– Scott Craner, Commented Dec 18, 2015 at 15:55

Ambie · Accepted Answer · 2015-12-20 10:30:36Z

0

All of the comments made to your answer would work well for you. It's also true that there's no evidence in your code at having attempted something, however rudimentary, and this is why answers to a rather trivial task are not forthcoming for you. Perhaps, in future, have a go at some kind of solution ( even if it feels more guesswork than anything) and people on this site will be much more supportive of you.

To set you on your way, you could make use of the Split() function which converts a String into a String array, separated by a nominated value - in the case of your quotations, you could use "-" as your separator. This might be easier than your Mid function and will deal with the case of different sized quotations.

The code below will get you started but you'd want some error handling in there to test, for example, that each cell splits appropriately or that any cells aren't blank. I'll leave all of that to you.

Option Explicit
Private mLastQuote As Long

Public Sub Test()
    Initialise 'call this routine just once at the start of your project
    MsgBox GetNextQuote(16) 'use the GetNextQuote() function to get next number
    MsgBox GetNextQuote(16)
    MsgBox GetNextQuote(16)
End Sub

Private Function GetNextQuote(prefix As Integer) As String
    mLastQuote = mLastQuote + 1
    GetNextQuote = CStr(prefix) & "-" & _
                   Format(mLastQuote, "000#")
End Function

Private Sub Initialise()
    Const PATH_NAME As String = "C:\Users\Rich\Documents\Bryan\BigProject\"
    Const FILE_NAME As String = "QuoteLog2016.xlsx"
    Const QUOTE_COL As String = "B"

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim v As Variant
    Dim r As Long
    Dim parts() As String
    Dim num As Long

    Application.ScreenUpdating = False
    Set wb = Workbooks.Open(PATH_NAME & FILE_NAME, True, True)
    Set ws = wb.Worksheets("Sheet1")

    'Read quote values into variant array
    With ws
        v = .Range(.Cells(2, QUOTE_COL), _
            .Cells(.Rows.Count, QUOTE_COL).End(xlUp)) _
            .Value2
    End With

    'Find max quote
    For r = 1 To UBound(v, 1)
        parts = Split(v(r, 1), "-") 'splits quote into 3 parts
        num = CLng(parts(1)) 'index (1) is the middle part
        If num > mLastQuote Then mLastQuote = num
    Next

    wb.Close False
    Application.ScreenUpdating = True
End Sub

answered Dec 20, 2015 at 10:30

Ambie

4,9872 gold badges16 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user3822047 Over a year ago

Hi, Ambie. Thanks for the guidance on how to submit questions showing what programming i have tried. I understand what you are saying.

user3822047 Over a year ago

My original question did not have code in it because I know I didn't try anything yet. I was trying to find out if there was a way to submit that Mid result to the MAX function so that I didn't have to write a loop that might take way more time that the Max function. I spent a lot of time trying to Google help on this, but I was not able to ever find an answer. Apparently, there is no tricky way to imbed the mid into the Max function. I did implement the loop as suggested and it is working. Thanks all.

Ambie Over a year ago

Glad it helped. If you do want to use the MAX function then you'd need to create a Range or Array containing only the middle part. You could use @ScottCraner's answer to create a new column and MAX that column or you'd have to loop through the quotations to create an array of only the middle parts, as @JohnColeman suggests. Bear in mind that MAX runs an internal loop, so the code you have implemented will be more or less as efficient as any other.

Collectives™ on Stack Overflow

Excel VBA - how to find the largest substring value in a column

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related