set variable to row value of date found in range

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
LVL 6
KnutsonBMAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
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

0
 
CluskittCommented:
=ROW(VLOOKUP(TODAY(),Charts!A:A,1,0))
0
 
Saqib Husain, SyedEngineerCommented:
try

worksheetfunction.match(date,Sheets("Charts").range("A:A"),0)
0
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
Saqib Husain, SyedEngineerCommented:
FindToday = worksheetfunction.match(date,Sheets("Charts").range("A:A"),0)
0
 
KnutsonBMAuthor 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'
0
 
Rob HensonConnect With a Mentor Finance AnalystCommented:
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
0
All Courses

From novice to tech pro — start learning today.