[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 262
  • Last Modified:

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
0
KnutsonBM
Asked:
KnutsonBM
2 Solutions
 
CluskittCommented:
=ROW(VLOOKUP(TODAY(),Charts!A:A,1,0))
0
 
Saqib Husain, SyedEngineerCommented:
try

worksheetfunction.match(date,Sheets("Charts").range("A:A"),0)
0
 
Saqib Husain, SyedEngineerCommented:
FindToday = worksheetfunction.match(date,Sheets("Charts").range("A:A"),0)
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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
 
Rob HensonIT & Database AssistantCommented:
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now