CODEWITHSUNDEEP
excelvbastringsubstringmax
eirikdaude
eirikdaude

Reputation: 3256

Max of substring in column

Given that I have a column of values in the format

01.2020
12.2021
3.2019
02.2020
etc.

over a million rows or so, I want a VBA function to find the maximum value of the digits to the left of the period.

Something like

Option Explicit

Sub test()
    Dim r As Range, c As Range
    Dim max As Long, current As Long
    Dim s As String
    
    With År_2020
        Set r = .Range(.Range("D2"), .Range("D" & .Rows.Count).End(xlUp))
    End With
    
    For Each c In r
        current = CLng(Left(c, InStr(1, c, ".", vbBinaryCompare) - 1))
        If current > max Then max = current
    Next c
    
    Debug.Print max
End Sub

works, but I feel like there ought to be a simpler solution.

Can someone please give some input on whether they can find a simpler solution?

Upvotes: 2

Views: 426

Answers (3)

Tom Sharpe
Tom Sharpe

Reputation: 34180

I always like to propose a naive solution. On the face of it you could do this:

=TRUNC(MAX(--D:D))

(leave the truncation till the end because the part of the number after the decimal point doesn't affect the result)

As pointed out below, this only works correctly for those locales that use a decimal point (period) not a decimal comma.

Upvotes: 1

ZygD
ZygD

Reputation: 24356

Seems to work. And it is short.

Sub test()
    Dim max As Long
    max = [MAX(TRUNC(D:D))]
    Debug.Print max
End Sub

Upvotes: 1

Pᴇʜ
Pᴇʜ

Reputation: 57673

Formula solution

enter image description here

  • formula in B1: =VALUE(LEFT(A1,FIND(".",A1)-1))
  • in C1: =MAX(B:B)

VBA solution

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")

Dim LastRow As Long 'find last used row
LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

Dim ValuesToConvert() As Variant 'read data into array
ValuesToConvert = ws.Range("A1", "A" & LastRow).Value 'will throw an error if there is only data in A1 but no data below, make sure to cover that case

Dim iVal As Variant
For iVal = LBound(ValuesToConvert) To UBound(ValuesToConvert)
    'truncate after period (might need some error tests if period does not exist or cell is empty)
    ValuesToConvert(iVal, 1) = CLng(Left(ValuesToConvert(iVal, 1), InStr(1, ValuesToConvert(iVal, 1), ".") - 1))
Next iVal

'get maximum of array values
Debug.Print Application.WorksheetFunction.Max(ValuesToConvert)

Upvotes: 2

Related Questions