Find text and return the row number

[ankita.sethi]

Hi,

I am trying to write a VBA code where it takes a text value (given by the user( eg "reportname" , and searches it in a particular range (eg. A6 to A17), and then returns the row number in which the text was found.

Please help!

 

[VoG]

Try

Sub test()
Dim Found As Range
Set Found = Range("A6:A17").Find(what:=InputBox("Enter search term"), LookIn:=xlValues, lookat:=xlWhole)
If Found Is Nothing Then
    MsgBox "Not found", vbExclamation
Else
    MsgBox "Found in row " & Found.Row, vbInformation
End If
End Sub

 

[yogeshmaney]

provide data sample please

 

[jim may]

In a standard module paste in:

Sub Foo()
Res = InputBox("Enter text for search")
With Sheet1.Range("A6:A17")
    Set c = .Find(Res, lookat:=xlWhole)
    If c Is Nothing Then
    MsgBox "Item not found"
    Exit Sub
    Else
    MsgBox "Your text is in row number " & c.Row
    End If
End With
End Sub