excel - 列中子字符串的最大值

Question

鉴于我有一列格式的值

01.2020
12.2021
3.2019
02.2020
etc.

超过一百万行左右，我想要一个 VBA 函数来找到句点左侧数字的最大值。

就像是

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

有效，但我觉得应该有一个更简单的解决方案。

有人可以就他们是否能找到更简单的解决方案提供一些意见吗？

标签： excelvbasubstringmaxworksheet-function

Answer 1

配方解决方案

• B1中的公式：=VALUE(LEFT(A1,FIND(".",A1)-1))
• 在 C1 中：=MAX(B:B)

VBA解决方案

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)