Find string/date in array to get value of nearby cell

[Jason]

I have an array that contains all sorts of text in column A.
I would like to know how I can search for certain date (example "6-Aug") and
then get the value of the cell that is 5 rows below that date?

The date is a web query where the only pattern I can find is the data I need
for each date is 5 rows below the corresponding date.

Thanks!

 

[Jason]

Preferably in VBA format

 

[Tim Li - MSFT]

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")

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/library/aa195730(office.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/library/aa224911(office.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:
(e-mail address removed).

 

[Jacob Skaria]

Hi Jason

The below macro will return the entry 5 rows down from the date entry
06-Aug-2009 in Column A. Hope the date entry is in excel date format. Try and
feedback

Sub Macro()

Dim dtTemp As Date, lngRow As Long
dtTemp = DateValue("06-Aug-2009")
lngRow = WorksheetFunction.Match(dtTemp, Range("A:A").Value, 0)
MsgBox Range("A" & lngRow + 5)

End Sub

If this post helps click Yes

 

[Jason]

It's giving me a Run-time error '13' Type Mismatch on the lngRow line no
matter what I try.

 

[Dave Peterson]

Option Explicit
Sub testme()

Dim res As Variant
Dim myDate As Date
Dim myRng As Range

myDate = DateSerial(2009, 8, 6) 'August 6, 2009

With Worksheets("Somesheetnamehere")
Set myRng = .Range("A:A")
End With

res = Application.Match(CLng(myDate), myRng, 0)

If IsError(res) Then
MsgBox "Not found"
Else
MsgBox myRng(res).Offset(5, 0).Value
End If
End Sub

 

[Dave Peterson]

Ps.

I find the .offset() easier to understand, but this would have worked, too:

MsgBox myRng(res + 5).Value

 

[Jason]

Thanks! That did the trick!

 

[Jason]

Thanks! That worked as well!