try

Sub myDateRow()
Dim a As Date
a = Sheets("Shares").Range("D9")

With Sheets("Sec1").Range("A1:A500")
.Range("A500").Select
.Cells.Find(What:=a, After:=ActiveCell, _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Activate
End With
End Sub



"Bruce" wrote:

I would like to return the row number in which there is a match to a my
variable.

In this case a is the value I am looking for and its in column a of
Sheets("Sec1").Range("A1:A500")

How should I go about this? Am i going in the right direction by using
Cells.Find?

Bruce

Sub myDateRow()

a = Format(Sheets("Shares").Range("D9"), "yyyymmdd")

With Sheets("Sec1").Range("A1:A500")
Range("A1").Select
Cells.Find(What:=""" & a & """, After:=ActiveCell,
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:=False).Activate
End With
End Sub