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
6
Medium Priority
?
257 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
[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
  • Learn & ask questions
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
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!

 
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 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
    MsgBox "Not found"
Else
    MsgBox "Found on row " & FindToday
End If
End Sub

0
 
LVL 33

Assisted Solution

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

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

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!

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…

636 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