CODEWITHSUNDEEP
excelvba
user3822047
user3822047

Reputation: 61

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

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

Upvotes: 0

Views: 1350

Answers (1)

Ambie
Ambie

Reputation: 4977

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

Upvotes: 0

Related Questions