finding the date in a sheet

First, Name your data range B2:H5202 as DATA
Then, insert the formula =Today() in an unused cell and
name that cell TODAY

Switch to VBA and insert a module if one does not exist.

Copy the following to the new module:

Public Sub FindDate()
Dim intCount As Integer
Dim MyRange As Range
Set MyRange = Range("Data")
Dim rngFoundCell As Range

intCount = MyRange.Count

For x = 1 To intCount
If MyRange.Cells(x) = Range("Today") Then
Set rngFoundCell = MyRange.Cells(x)
rngFoundCell.Offset(1, 0).Select
Exit Sub
End If

Next x
End Sub


You could also add the following for the worksheet event,
so that whenever you activate that sheet, if correct cell
is active.

Private Sub Worksheet_Activate()
FindDate
End Sub




-----Original Message-----

i have a sheet in the range of B2 till H5202 this sheet
contains dates
for the whole year, i want to automate so that the sheet
when it opens
jumps to the date of today and then moves 1 cell below,
wonder if
anyone can help me.

Thanks in advance


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step
guide to creating financial statements
.

Slight Change to notify the user if the Date is not found.

Public Sub FindDate()
Dim intCount As Integer
Dim MyRange As Range
Set MyRange = Range("Data")
Dim rngFoundCell As Range

intCount = MyRange.Count

For x = 1 To intCount
If MyRange.Cells(x) = Range("Today") Then
Set rngFoundCell = MyRange.Cells(x)
rngFoundCell.Offset(1, 0).Select
Exit Sub
End If

Next x

MsgBox "Date Not Found"

End Sub

DHymel

-----Original Message-----
First, Name your data range B2:H5202 as DATA
Then, insert the formula =Today() in an unused cell and
name that cell TODAY

Switch to VBA and insert a module if one does not exist.

Copy the following to the new module:

Public Sub FindDate()
Dim intCount As Integer
Dim MyRange As Range
Set MyRange = Range("Data")
Dim rngFoundCell As Range

intCount = MyRange.Count

For x = 1 To intCount
If MyRange.Cells(x) = Range("Today") Then
Set rngFoundCell = MyRange.Cells(x)
rngFoundCell.Offset(1, 0).Select
Exit Sub
End If

Next x
End Sub


You could also add the following for the worksheet event,
so that whenever you activate that sheet, if correct cell
is active.

Private Sub Worksheet_Activate()
FindDate
End Sub




-----Original Message-----

i have a sheet in the range of B2 till H5202 this sheet
contains dates
for the whole year, i want to automate so that the sheet
when it opens
jumps to the date of today and then moves 1 cell below,
wonder if
anyone can help me.

Thanks in advance


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by
step
guide to creating financial statements
.

.