Hello ,

I've little confused about the relationship between the array and the date
you want to find("6-Aug"?), is the date contains in Column A? My
understanding is you want find a cell which is 5 rows below a Column A
cell. If I'm correct the solution is shows as below:

Sub FindValue()
Dim rFound As range
Dim rng As Excel.range
Set rng = Me.range("D:D")

On Error Resume Next
With rng
Set rFound = .Find(What:="6-Aug", LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False)
If Not rFound Is Nothing Then
MsgBox rFound.Offset(5, 0).Value2
End If
On Error GoTo 0
If Not rFound Is Nothing Then Application.Goto rFound, True
End With

End Sub

Some comment about above solution, the idea is we could take use of
Range.Find method to search a string in a certain range, for more
information about Find method , please refer to this link:
http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

After we get the cell that contains "6-Aug" we then use Range.Offset
property to get the final cell we want. For more information about Offset
property please refer to this link:
http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

Besides, a more complex case is if the value you search is a Date Type data
in Excel, please refer to this article:
http://www.ozgrid.com/VBA/find-dates.htm


Best regards,
Tim Li
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
.

Thanks! That did the trick!