PDA

View Full Version : [SOLVED] Date value error, need help



JackkG
04-06-2015, 01:21 PM
Hi,

I got to take the minimum date value from three date values in a row. The code I'm using is below:
But when one of the three date value is blank it returns just "1/1/1900 12:00:00". Is there any way to check for this to skip the blank values and to take lowest date from the remaining two date values?



dim dateval as Date
dateval=WorksheetFunction.Min(cell.Offset(0, 9).Value, cell.Offset(0, 10).Value, cell.Offset(0, 11).Value)
cell.Offset(0, 3).Value = dateval

mperrah
04-07-2015, 12:00 PM
Try this.
I entered random time values in I1, J1 and K1
Then removed 1, 2 or 3 values and it reports the lowest time,
unless all are removed then it displays the current time..


Sub findMin()
Dim x, r As Long

Cells(1, 3).FormulaR1C1 = "=NOW()"
With Range("A1")
r = 1
For x = 9 To 11 ' change to suit, can be a variable based on used columns
If Cells(1, x).Value <> "" And _
Cells(1, x).Value < Cells(1, 3).Value Then
Cells(1, 3).Value = Cells(1, x).Value
End If
Next x
End With
Cells(1, 3).NumberFormat = "h:mm:ss;@"

End Sub


hope this helps,

-mark

JackkG
04-07-2015, 03:23 PM
Thanks Mark, this works great!!

mperrah
04-07-2015, 03:33 PM
:cloud9: Glad to help