set variable to row value of date found in range

KnutsonBM
KnutsonBM used Ask the Experts™
on
in Column A on Sheets("Charts") there is a list of dates.  I need to set variable FindToday to the row number of today's date
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
=ROW(VLOOKUP(TODAY(),Charts!A:A,1,0))
try

worksheetfunction.match(date,Sheets("Charts").range("A:A"),0)
FindToday = worksheetfunction.match(date,Sheets("Charts").range("A:A"),0)
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Author

Commented:
Sub Asearch()
Dim FindToday As Integer
FindToday = WorksheetFunction.Match(Date, Sheets("Charts").Range("A:A"), 0)
If FindToday Is Nothing Then
    MsgBox "Not found"
Else
    MsgBox "Found on row " & FindToday
End If
End Sub

this gives me a 'Type Mismatch'
Then try this

Sub Asearch()
Dim FindToday As Integer
On Error Resume Next
FindToday = Application.Match(Date * 1, Sheets("Charts").Range("A:A"), 0)
On Error GoTo 0
If FindToday = 0 Then
    MsgBox "Not found"
Else
    MsgBox "Found on row " & FindToday
End If
End Sub

Rob HensonFinance Analyst
Commented:
Alternative:

For Each Cell in Range("A:A")

If Cell.Value2 = Date * 1
Then R = Cell.Row
Exit Sub
End If
Next Cell

Open in new window


This will give the first occurence of today's date, if you want the last occurence remove line 5 "Exit Sub".

Thanks
Rob H

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial