How to capture row of a string value

[Glen Mettler]

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

 

[Ron de Bruin]

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:=FindString, _
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:=FindString, _
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