Solved

set variable to row value of date found in range

Posted on 2011-09-06
6
204 Views
Last Modified: 2012-05-12
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
Comment
Question by:KnutsonBM
6 Comments
 
LVL 18

Expert Comment

by:Cluskitt
ID: 36491256
=ROW(VLOOKUP(TODAY(),Charts!A:A,1,0))
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 36491268
try

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

Expert Comment

by:Saqib Husain, Syed
ID: 36491284
FindToday = worksheetfunction.match(date,Sheets("Charts").range("A:A"),0)
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 6

Author Comment

by:KnutsonBM
ID: 36491294
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
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 250 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
    MsgBox "Not found"
Else
    MsgBox "Found on row " & FindToday
End If
End Sub

0
 
LVL 31

Assisted Solution

by:Rob Henson
Rob Henson earned 250 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

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now