Get the row number that matches my value

[Bruce]

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

[gocush]

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
>

[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

"Bruce" <Bruce@discussions.microsoft.com> wrote in message
news:272F4BDD-D586-431D-A180-16D8B9DB7C45@microsoft.com...
>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
>