CODEWITHSUNDEEP
excelvbadate
redd
redd

Reputation: 145

How to find earliest date from a list of dates in a column?

I have taken an existing code and tried to adapt it to what I want to do, but I have trouble with that process.

The column size(number of dates) would vary in each excel sheet I would process. Each cell would defintely contain a date in the format dd/mm/yyyy.

Run-time Error '13' Type mismatch

Any idea how I can get around this?

Dim j As Integer, minValue As Date
Dim t0 As Double, t1 As Double
Dim ans As Date

t0 = CDbl(DateSerial(2000, 1, 1))
t1 = CDbl(DateSerial(2100, 12, 31))
ans = 0
For j = 3 To 7
    If ans = 0 Then ' You need to store the first valid value
        If Sheets(1).Cells(j, 9).Value >= t0 And Sheets(1).Cells(j, 9) <= t1 Then
            ans = Sheets(1).Cells(j, 9).Value
        End If
    Else
        If (Sheets(1).Cells(j, 9).Value >= t0 And Sheets(1).Cells(j, 9) <= t1) _
           And Sheets(1).Cells.Value < ans Then
            ans = Sheets(1).Cells(j, 9).Value
        End If
    End If
Next j

Upvotes: 2

Views: 2627

Answers (2)

Excel Hero
Excel Hero

Reputation: 14764

Here is a more direct approach:

[sheet2!a1] = cdate([small(sheet1!i:i,1)])

Upvotes: 0

redd
redd

Reputation: 145

I was able to use Sam's comment to answer my own question. Here is a working macro that solves my problem:

Range("I3:I7").Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("I3"), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
    .SetRange Range("I3:I7")
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
Range("I3").Select
Selection.Copy
Sheets("Sheet2").Select
Range("AA1").Select
ActiveSheet.Paste

Upvotes: 2

Related Questions