Dates are mysterious things. Sometimes you can just look for them--like:

Option Explicit
Sub testme()

Dim Wks As Worksheet
Dim FoundCell As Range
Dim myRng As Range
Dim WhatToFind As Date

Set Wks = ActiveSheet
Set myRng = Wks.Range("A1:U200")

WhatToFind = DateSerial(2010, 2, 28)

With myRng
Set FoundCell = .Cells.Find(what:=WhatToFind, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
End With

If FoundCell Is Nothing Then
MsgBox WhatToFind & " wasn't found"
Else
With FoundCell.Offset(1, 0)
MsgBox .Value & vbLf & .Address
End With
End If

End Sub

Sometimes, you need to use:
Set FoundCell = .Cells.Find(what:=clng(WhatToFind), _

or maybe
Set FoundCell = .Cells.Find(what:=format(WhatToFind, "mm/dd/yyyy"), _

(match the numberformat that you use.)




nelly wrote:

HI does anyone know of a workaround as a formula for this VBA code.

Range("A1:U200").Select
test = Selection.Find(What:="40237", After:=ActiveCell,
LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Address

I thought it easier to explain this way.

40237 is a date and can be anywhere in the above range. I need to get the
value from the cell below.

Thanks in advance

Nelly

--

Dave Peterson