Excel – Find row containing specific value

excelvba

I have an Excel worksheet where every cell in column A is a date in sequential order. 

    A    
----------
1/01/2016
...
12/31/2019

How can I return the row number of where a specific date is found?

Something like this:

Private Sub btnSubmit_Click()

    today = Date

    row_today = ThisWorkbook.Sheets("Sheet1").Range("A:A").Find(What:=today, _
        LookIn:=x1Values)

    MsgBox row_today

End Sub

Best Answer

It is good practice to declare your variables at the start of your subroutine, in this case it would have helped you find the error. The other problem is that .Find is currently returning a range, where you would like to see the row number of that range. I'll explain how to solve both problems below:

Declaring variables

In your code you want to return:

  • today as a date and
  • row_today as a long integer

Edit: I previously recommended declaring row_today as integer. This will give errors if the row number is greater than 32,767; so I have amended to long, which can comfortably handle more than 2 million rows

But you are currently returning:

  • today as a Variant and
  • row_today as a Variant

You can declare them like this:

Dim today As Date
Dim row_today As Long

Using .Find

In this line of your code:

row_today = ThisWorkbook.Sheets("Sheet1").Range("A:A").Find(What:=today, LookIn:=x1Values)

Firstly, you have a typo - it should be LookIn:=xlValues not LookIn:=x1Values

Secondly, you are returning the range of the cell that contains the date you are looking for. For more information on the .Find method, read the MSDN Doc here.

To return the row number, you only need to add .Row at the end like this:

row_today = ThisWorkbook.Sheets("Sheet1").Range("A:A").Find(What:=today, LookIn:=x1Values).Row

So your final code should look something like this:

Private Sub btnSubmit_Click()

Dim today As Date
Dim row_today As Long

today = Date
row_today = ThisWorkbook.Sheets("Sheet1").Range("A:A").Find(What:=today, LookIn:=xlValues).Row

Debug.Print row_today

End Sub
Related Topic