How to search data and return row number?

Question

I am trying to search x for example through a sheet of data. I thought of using Match function but match function only look up to only one column. x could be anywhere in that sheet, so is that any function or anything which can return the row number which can search all data?

I have added codes in the excel and now the error of subscript out of range is appearing. I am not really sure if I did my find function correctly

Dim r As range 
With Sheets("Sheet2").Range("A:DZU")
    Set r = .Find(What:="U1")
End With

yes, Google Find , or Range.Find you''' get plenty of results. — Shai Rado
– Shai Rado, Commented May 2, 2017 at 6:10
If you have a Range, you can always find the row number: Range.Row — Rich Holton
– Rich Holton, Commented May 2, 2017 at 6:12
@ShaiRado, I used the find function on the excel sheet itself but it couldn't find it. But when i used the find & select, excel could find it. Don't understand why... — Rachel Chia
– Rachel Chia, Commented May 2, 2017 at 6:17
@RachelChia please share your code attempt and worksheet. so we can take a look — Shai Rado
– Shai Rado, Commented May 2, 2017 at 6:29

Shai Rado · Accepted Answer · 2017-05-02 06:47:26Z

2

Try the code below using the Find function to look for "U1" in Columns "A:DZU".

Option Explicit

Sub FindX()

Dim FndRng As Range

With Sheets("Sheet2").Range("A:DZU")
    Set FndRng = .Find(What:="U1", LookIn:=xlValues, LookAt:=xlWhole)        
    If Not FndRng Is Nothing Then ' <-- Find was successful
        MsgBox "Found `U1` at row " & FndRng.Row
    Else
        MsgBox "Unable to find `U1`"
    End If
End With

End Sub

answered May 2, 2017 at 6:47

Shai Rado

33.7k7 gold badges32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rachel Chia Over a year ago

Excel is pretty weird I used With Sheet2.Range("B:G") instead of With Sheets("Sheet2").Range("A:DZU") and it works but I will combine my codes with your codes. Thanks for your help !

score 1 · Accepted Answer · 2017-05-02 06:47:04Z

1

This might be helpful

Sub GetRowNum()
    Dim myValue As String
    myValue = "x"

    Dim wb As Workbook
    Dim ws As Worksheet
    Set wb = ActiveWorkbook
    Set ws = ActiveSheet

    Debug.Print ws.Range("A:Z").Find(myValue).Row

End Sub

Edit: added print for visualisation

edited May 2, 2017 at 6:47

answered May 2, 2017 at 6:31

user7857211

Collectives™ on Stack Overflow

How to search data and return row number?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related