CODEWITHSUNDEEP
vbaexcel
Chris TJ
Chris TJ

Reputation: 53

Find the earliest date in VBA

I have a lot of dates in column D. I need to find the student with the earliest date, and show the following information in a messagebox:

Sub Finddate()
    Dim Mn As Integer
    Mn = Application.Match(Application.Min(Range("D2:D18288")), Range("D2:D18288"), 0)
    MsgBox ("For the student with the earliest date (" & Range("D" & Mn) & ") the following information applies: " & Range("k" & Mn) & ", " & Range("L" & Mn) & " and " & Range("M" & Mn))
End Sub

However when i run the Macro it shows the wrong date. The earliest date in the sheet is 31-08-1996, but it says the earliest date is 01-02-2010 and if i write =min(D2:D18288) in Excel it finds the right date. But i need it to work in VBA as well. And if i change min to max it also finds the wrong date. But if i instead write:

Mn = Application.Match(Application.Max(Range("D2:D18288")), Range("D2:D18288"))

It shows the right date but i need to find the min date not the max date and when i change max to min I get a type mismatch error. I really don’t know what is wrong really hope someone can help me!

Upvotes: 2

Views: 3145

Answers (2)

Vityata
Vityata

Reputation: 43575

When something like this happens, try to replicate the result, using a small sample. E.g. this one, hoping to return Peter6 for the smallest info:

enter image description here

Option Explicit

Public Sub TestMe()

    Dim dateRanges As Range
    Set dateRanges = Range("D1:D11")

    Dim mn As Variant
    With Application
        mn = .Match(.Min(dateRanges), dateRanges, 0)
    End With

    MsgBox Range("E" & mn).Value2

End Sub

Once it works, try to fix it with your big example.

You will probably notice that mn should not be Integer as far as Integer is up to 32767 and this parsed to a date is 16-September-1989, which is long time ago. In your case it is not an error, because you are not referencing mn directly to a date, but it may happen at a later.

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96753

Your indexing is off by 1 ................because the data starts out a D2 rather than D1, Mn points to the cell just above the minimum.

Upvotes: 2

Related Questions