CODEWITHSUNDEEP
vbaexcel
calicationoflife
calicationoflife

Reputation: 291

VBA: find second largest value

I have the following problem: I try to filter a date column (A) in a worksheet (HISTORICALS) to return the highest and second highest date value. Currently, there is dates ranging from the 25th to the 31st of December in this column. Unfortunately, below formula (using the Large function) returns the 31st two times (and not the 30th and 31st as intended).

Sub Select_Last_Two_Days()

    With Worksheets("HISTORICALS")
        Highest_Max = Format(WorksheetFunction.Large(Worksheets("HISTORICALS").Range("A:A"), 1), "Short Date")
        Second_Highest_Max = Format(WorksheetFunction.Large(Worksheets("HISTORICALS").Range("A:A"), 2), "Short Date")
        Debug.Print Highest_Max, Second_Highest_Max
    End With

End Sub

The column has approx. 2000 rows, with dates occuring multiple times. So ideally I would want to filter for distinct values and then return the two highest dates. Any idea how I can do that?

Upvotes: 2

Views: 5960

Answers (2)

QHarr
QHarr

Reputation: 84465

The recommendations given in the comments are probably the simplest and least amount of code way to do things but here is another sugggestion:

Sub test()

Dim wb As Workbook
Dim ws As Worksheet

Set wb = ThisWorkbook
Set ws = wb.Worksheets("HISTORICALS")

Dim lastRow As Long

lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

Dim loopArr()

loopArr = ws.Range("A1:A" & lastRow).Value

Dim maxVal As Date

maxVal = Application.WorksheetFunction.Large(ws.Range("A1:A" & lastRow), 1)

Dim i As Long
Dim secondVal As Date

   For i = UBound(loopArr, 1) To LBound(loopArr, 1) Step -1

        If loopArr(i, 1) < maxVal Then
          secondVal = loopArr(i, 1)
          Exit For
        End If
   Next i

End Sub

Upvotes: 1

Axel Richter
Axel Richter

Reputation: 61870

Simply translate Barry Houdinis answer from How to find the first and second maximum number? to VBA:

Sub Select_Last_Two_Days()

    With Worksheets("HISTORICALS")
        Highest_Max = Format(WorksheetFunction.Max(.Range("A:A")), "Short Date")
        Second_Highest_Max = Format(WorksheetFunction.Large(.Range("A:A"), WorksheetFunction.CountIf(.Range("A:A"), WorksheetFunction.Max(.Range("A:A"))) + 1), "Short Date")
        Debug.Print Highest_Max, Second_Highest_Max
    End With

End Sub

Upvotes: 2

Related Questions