Hi Nelly

See if this can help you:

Sub aaa()
Dim SearchRng As Range
Dim SearchValue As Long
Dim f

SearchValue = 40237
Set SearchRng = Range("A1:U200")
Set f = SearchRng.Find(What:=SearchValue, After:=Range("A1"),
LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If f Is Nothing Then
msg = MsgBox("No match")
Else
Result = f.Offset(1, 0).Value
End If
End Sub

Regards,
Per

"nelly" skrev i meddelelsen
...
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