Get the row number that matches my value

[Guest]

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

 

[Guest]

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

 

[Tim Williams]

Try this:


Function GetRow(sText) As Long

Dim lRow As Long
lRow = 0

On Error Resume Next
lRow = Sheets("Sheet1").Range("A1:A500").Find(What:=sText, _
LookIn:=xlValues, LookAt:=xlPart,
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Row
On Error GoTo 0

GetRow = lRow

End Function


Tim