Return ROW number from qa found range

I have the following:

Columns("A:A").Select
Selection.Find(What:="Alabama", After:=ActiveCell, LookIn:=xlFormulas,
lookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False).Activate

Now want to have a variable equal to the Row number that it finds the text.

Try something like

Dim FoundCell As Range
Dim WhatRow As Long
Columns("A:A").Select
Set FoundCell = Selection.Find(What:="Alabama",
After:=ActiveCell, _
LookIn:=xlFormulas, lookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If FoundCell Is Nothing Then
MsgBox "Not Found"
Else
WhatRow = FoundCell.Row
MsgBox "Found in row: " & WhatRow
End If



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Mike" <[email protected]> wrote in message
news:[email protected]...
>I have the following:
>
> Columns("A:A").Select
> Selection.Find(What:="Alabama", After:=ActiveCell,
> LookIn:=xlFormulas,
> lookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> MatchCase:=False).Activate
>
> Now want to have a variable equal to the Row number that it
> finds the text.
>

Hi Mike,

rownumber=activecell.row

in your case

Regards,
Ivan

One way:

Given your existing code:

nRow = ActiveCell.Row

Perhaps a better way:

Dim nRow As Long
Dim rFound As Range
Set rFound = Range("A:A").Find( _
What:="Alabama", _
After:=Range("A1"), _
LookIn:=xlFormulas, _
LookiAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rFound Is Nothing Then
nRow = rFound.Row
Else
MsgBox "'Alabama' was not found in column A"
End If





In article <[email protected]>,
Mike <[email protected]> wrote:

> I have the following:
>
> Columns("A:A").Select
> Selection.Find(What:="Alabama", After:=ActiveCell, LookIn:=xlFormulas,
> lookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> MatchCase:=False).Activate
>
> Now want to have a variable equal to the Row number that it finds the text.