I need to capture the row of a selected text value. Example:

Sheets(1).Range("A1").value = "LM "
"LM" is also located somewhere in column F of Sheet2
I need to lookup LM in column F of sheet2 and capture the row.

How can I do that either as a formula in a cell or vb code?

Glen

Hi Glen

Try this example

Sub Find_First()
Dim FindString As String
Dim Rng As Range
FindString = "LM"
Set Rng = Sheets("sheet2").Range("F:F").Find(What:=FindStrin g, _
After:=Range("F" & Rows.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
MsgBox Rng.Row
Else
MsgBox "FindString is Not in the column"
End If
End Sub

Or with a inputbox

Sub Find_First()
Dim FindString As String
Dim Rng As Range
FindString = InputBox("Enter a Search value")
If Trim(FindString) < "" Then
Set Rng = Sheets("sheet2").Range("F:F").Find(What:=FindStrin g, _
After:=Range("F" & Rows.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
MsgBox Rng.Row
Else
MsgBox "FindString is Not in the column"
End If
End If
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Glen Mettler" wrote in message ...
I need to capture the row of a selected text value. Example:

Sheets(1).Range("A1").value = "LM "
"LM" is also located somewhere in column F of Sheet2
I need to lookup LM in column F of sheet2 and capture the row.

How can I do that either as a formula in a cell or vb code?

Glen