How to find column with oldest date?

[rizzo93]

I have a row containing formulas that will either show a date or a "".

A	B	C	D	E	F
6/5	6/4	6/3	6/2

Using VBA, I want to find the column where the oldest date is located in that row. So in the example above, I need to identify the column where 6/2 is located.

How can I do this please?

 

[Fluff]

How about

Application.min(range("A1:F1"))

 

[rizzo93]

Hi Fluff and thank you!

I should have been more clear: I need to know which column that smallest value is located.

 

[Fluff]

How about

   Dim fnd As Range
   Dim Mn As Long
   
   Mn = Application.min(Range("A1:F1"))
   Set fnd = Range("A1:F1").Find(CDate(Mn), , xlValues, xlWhole, , , , , False)
   MsgBox fnd.Column

 

[rizzo93]

How about

   Dim fnd As Range
   Dim Mn As Long
  
   Mn = Application.min(Range("A1:F1"))
   Set fnd = Range("A1:F1").Find(CDate(Mn), , xlValues, xlWhole, , , , , False)
   MsgBox fnd.Column

I tried your code with the actual range replaced with my own, but I'm getting an error:

Also, I usually use Application.WorksheetFunction.Min but I noticed WorksheetFunction missing from your code, so is that no longer necessary? Would be useful since it's less to remember.

 

[Fluff]

Are your dates real dates?

 

[rizzo93]

If by "real" you mean "m/d", then I'll answer it this way: the dates are formatted in the cells to show in the format of "m/d".

 

[Fluff]

If you change the format of the cell to general, what do you see?

 

[rizzo93]

I get the serial number for the date.

 

[rizzo93]

Or maybe "serial" isn't the right term. I'm seeing the number 43986.