VBA code to return row number of found value

[Majawat]

I have two sheets: Source and Database

Database has two columns, A is a list of names, B is a list of numbers

I would like a VBA code in Source to search for a user-inputted name inside Database column A and then return which row it's located.

Suggestions?

Thanks!

 

[VoG]

Try

Sub Asearch()
Dim found As Range
Set found = Sheets("Database").Columns("A").Find(what:=InputBox("Enter search term"), LookIn:=xlValues, lookat:=xlValues)
If found Is Nothing Then
    MsgBox "Not found"
Else
    MsgBox "Found on row " & found.Row
End If
End Sub

 

[Majawat]

I get a "Subscript out of range" error at

Set found = Sheets("Ratings").Columns("A").Find(what:=ActiveSheet.Cells(3, 1), LookIn:=xlValues, lookat:=xlValues)

"Ratings" is the true name of the Database. And I'm using the cell A3 as the input.

I notice LookIn is caps, but lookat isn't... Is there a reason for that?

 

[VoG]

Sorry, it should be

Set found = Sheets("Ratings").Columns("A").Find(what:=ActiveSheet.Cells(3, 1).Value, LookIn:=xlValues, lookat:=xlWhole)

 

[Majawat]

Perfect! Thanks a ton!

 

[ryaner84]

Just wanna said Thank you for sharing the code . I uses it, it work wonders.

 

[bharatmvs]

Sorry, it should be

Set found = Sheets("Ratings").Columns("A").Find(what:=ActiveSheet.Cells(3, 1).Value, LookIn:=xlValues, lookat:=xlWhole)

HI,
Can you please tell me how to return row numbers if the name exists more than once in the above code?

THank you,
Bharat

 

[mern1]

Do you know why this code wouldn't be working?

Private Sub CommandButton21_Click()Dim c As String
For Each c.Str In Worksheets("Sheet7").Range("C2")
   For Each c.Str In Worksheets("Sheet2").Range("E2:E895")
       n = found.Row
       project = Worksheets("Sheet2").Range("Ln")
       Weight = Worksheets("Sheet2").Range("Fn")
       MsgBox project, Weight
       
    Next c


Next c


End Sub