I'm trying to code a button on my calendar spreadsheet to find the cell for
today. My dates are functions from one cell g10 = 1/1/2009, g11 = g10+1,g12
= g11+1... and so on. The value for my what part is =today(). I'm not sure
if it's the range values being functions or the value looking for being
=today() that is messing it up or something else. I just want to have the
button find the cell in that range that equals the date. Here's my code that
I was trying to use. Thanks.

Dim FindCell As Range
With ActiveSheet
Set FindCell = .Range("g11:g2000").Find(what:=.Range("n1"))
If FindCell Is Nothing Then
MsgBox "Not found"
Else
FindCell.Activate
End If
End With

Dim FindCell As Range
Dim mydate As Date
mydate = Date
With ActiveSheet
Set FindCell = .Range("g11:g2000").Find(what:=mydate)
If FindCell Is Nothing Then
MsgBox "Not found"
Else
FindCell.Activate
End If
End With


Gord Dibben MS Excel MVP

On Wed, 23 Sep 2009 12:36:02 -0700, Jake F
wrote:

I'm trying to code a button on my calendar spreadsheet to find the cell for
today. My dates are functions from one cell g10 = 1/1/2009, g11 = g10+1,g12
= g11+1... and so on. The value for my what part is =today(). I'm not sure
if it's the range values being functions or the value looking for being
=today() that is messing it up or something else. I just want to have the
button find the cell in that range that equals the date. Here's my code that
I was trying to use. Thanks.

Dim FindCell As Range
With ActiveSheet
Set FindCell = .Range("g11:g2000").Find(what:=.Range("n1"))
If FindCell Is Nothing Then
MsgBox "Not found"
Else
FindCell.Activate
End If
End With

Given the structure of your data, you should be able to use this single
statement to activate the cell you want...

Range("G" & (10 + Date - Range("G10").Value)).Select

--
Rick (MVP - Excel)


"Jake F" wrote in message
...
I'm trying to code a button on my calendar spreadsheet to find the cell
for
today. My dates are functions from one cell g10 = 1/1/2009, g11 =
g10+1,g12
= g11+1... and so on. The value for my what part is =today(). I'm not
sure
if it's the range values being functions or the value looking for being
=today() that is messing it up or something else. I just want to have the
button find the cell in that range that equals the date. Here's my code
that
I was trying to use. Thanks.

Dim FindCell As Range
With ActiveSheet
Set FindCell = .Range("g11:g2000").Find(what:=.Range("n1"))
If FindCell Is Nothing Then
MsgBox "Not found"
Else
FindCell.Activate
End If
End With

Actually, this is probably a better single line statement version than my
previous posting...

Range("G10").Offset(Date - Range("G10").Value).Select

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Given the structure of your data, you should be able to use this single
statement to activate the cell you want...

Range("G" & (10 + Date - Range("G10").Value)).Select

--
Rick (MVP - Excel)


"Jake F" wrote in message
...
I'm trying to code a button on my calendar spreadsheet to find the cell
for
today. My dates are functions from one cell g10 = 1/1/2009, g11 =
g10+1,g12
= g11+1... and so on. The value for my what part is =today(). I'm not
sure
if it's the range values being functions or the value looking for being
=today() that is messing it up or something else. I just want to have
the
button find the cell in that range that equals the date. Here's my code
that
I was trying to use. Thanks.

Dim FindCell As Range
With ActiveSheet
Set FindCell = .Range("g11:g2000").Find(what:=.Range("n1"))
If FindCell Is Nothing Then
MsgBox "Not found"
Else
FindCell.Activate
End If
End With