Find lowest date and display cell ref.

[Sinbad]

Hi All,

need some help.

we have a bunch of books that are listed in Col A. Colb will be simple in and out selected from a dropdown.
I am using a little code to set a date in Col C depending on what is selected in Col B. If the book is "in" then the date in Col C is cleared, if the book is out, Col C will show the date it left. This works fine

        trg = Target.Address
        Vab = ActiveSheet.Range(trg).Value
        If Vab = "Out" Then
        ActiveCell.Offset(0, 1) = Format(Now(), "mm.dd.yyyy")
        Else
        ActiveCell.Offset(0, 1).Value = ""
        End If

now to my problem.
I would like to find the oldest date in Col C. Problem here is that there are blank cells and my code always returns the wrong date.

Dim lRow As Long
    
    lRow = Cells(Rows.Count, 2).End(xlUp).Row
    LowDate = Format(WorksheetFunction.Min(Range("C2:C" & lRow)), "mm.dd.yyyy")
    
   Debug.Print Format(LowDate, "mm.dd.yyyy")
        
    Cells.Find(What:=Range(LowDate), After:=ActiveCell, LookIn:=xlValues, LookAt _
    :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False, SearchFormat:=False).Activate

Ultimately I would like the lowest date copied to cell H2 and the ref. to which one it is into cell H4 (row number)

I only have 2 hair left and woul dlike to keep them. Any takers?

 

[Kamolga]

Hi,

To me blank cells made no problems but a value=0 does. So I decided to check if the cell was a date to consider it.

Sub LowDate()
Dim lRow As Long
Dim dCell As Range
Dim dRow As Long
Dim lDate As Date
 lDate = "31 / 12 / 2999"
 lRow = 0
lRow = Cells(Rows.Count, 2).End(xlUp).Row
For Each dCell In Range("C2:C" & lRow)
    If (IsDate(dCell) = True And dCell < lDate) Then
      lDate = dCell
      dRow = dCell.Row
    End If
Next dCell
   If lDate = "31 / 12 / 2999" Then
        Range("H2") = ""
        Range("H4") = ""
        Else
        Range("H2") = lDate
        Range("H4") = dRow
   End If
End Sub

Hope it will save both hairs

 

[Sinbad]

Hi,

neat. One problem though is the formatting of the date.

I got no results and decided to have it show me all the comparison it does. Your date being compare is in the format
31-Dec-2999 whilst the dates in the cells are formatted 05.29.2018. this leads to nothing being returned.

I tried adjusting your, but somehow it does not like what I do.

Any suggestions on that?

 

[Kamolga]

And if you put ldate = 1/1/2999 or 1.1.2999 or ldate= format ("31 / 12 / 2999","mm.dd.yyyy")?

If not, then maybe

[LEFT][COLOR=#333333][FONT=monospace]Sub LowDate()
Dim lRow As Long
Dim dCell As Range
Dim dRow As Long
Dim lDate As Date
 lDate = "31 / 12 / 2999"
 lRow = 0
lRow = Cells(Rows.Count, 2).End(xlUp).Row
For Each dCell In Range("C2:C" & lRow)
    If (IsDate(dCell) = True And format(dCell,"dd/mm/yyyy") < lDate) Then
      lDate = [COLOR=#333333][FONT=monospace]format(dCell,"dd/mm/yyyy")[/FONT][/COLOR]      
      dRow = dCell.Row
    End If
Next dCell
   If lDate = "31 / 12 / 2999" Then
        Range("H2") = ""
        Range("H4") = ""
        Else
        Range("H2") = format(lDate,[COLOR=#333333][FONT=monospace]"mm.dd.yyyy")[/FONT][/COLOR]        
        Range("H4") = dRow
   End If
End Sub[/FONT][/COLOR][/LEFT]

?

​

<strike></strike>
​

 

[Sinbad]

Hi,

I tried "lDate = Format(lDate, "mm.dd.yyyy")", obviously wrong. Your "lDate = Format("31 / 12 / 2999", "mm.dd.yyyy")" gives a type mismatch. Will try the new code.

But Thank you so far, both hair still safe

 

[Sinbad]

Ohhhh... WUNDERFUL..

Second code works like a charm. clever. Learnt something new.

Thank you.

 

[Kamolga]

You are welcome.

Seems VBA works in dd/mm/yyyyy format, which is strange since made in US.

Thank you for the feedback