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?
 
Saqib Husain, SyedEngineerCommented:
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
Cloud Class® Course: Amazon Web Services - Basic

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.

 
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 HensonFinance 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.