Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

set variable to row value of date found in range

Posted on 2011-09-06
Medium Priority
257 Views
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
Question by:KnutsonBM
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 18

Expert Comment

ID: 36491256
=ROW(VLOOKUP(TODAY(),Charts!A:A,1,0))
0

LVL 43

Expert Comment

ID: 36491268
try

worksheetfunction.match(date,Sheets("Charts").range("A:A"),0)
0

LVL 43

Expert Comment

ID: 36491284
FindToday = worksheetfunction.match(date,Sheets("Charts").range("A:A"),0)
0

LVL 6

Author Comment

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

this gives me a 'Type Mismatch'
0

LVL 43

Accepted Solution

Saqib Husain, Syed earned 1000 total points
ID: 36491721
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
Else
MsgBox "Found on row " & FindToday
End If
End Sub

0

LVL 33

Assisted Solution

Rob Henson earned 1000 total points
ID: 36494732
Alternative:

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

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

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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Suggested Courses
Course of the Month11 days, 21 hours left to enroll