finding the date in a sheet

  • Thread starter Thread starter bennyboy
  • Start date Start date
B

bennyboy

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

Thanks in advanc
 
B2:H5202 is a total of 36407 cells.
Which ones actually contain the dates?

bennyboy said:
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


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to
Click to expand...
creating financial statements
 
The actual cells that contain the dates are as follows:

J2 contains the current date with the Now() in it

B2 till H2
B102 till H102
B202 till H202
B302 Till H302
B402 Till H402
B502 Till H502
B602 Till H602
B702 Till H702
B802 Till H802
B902 Till H902
B1002 Till H1002
and so on the last row is B5202 Till H520
 
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


------------------------------------------------

~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step
Click to expand...
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
 
to all who are trying to help me, i did select a data range en named i
Data, the nex thing that i did was within that range a empty cell e
placed there =Today() and named that cell today

then i proceded to the VB and pasted the example into it, when i try t
run the VB script it return a error 6 overflow

debug points to the


intCount = MyRange.Count

please help me out with this one

Thank
 
There was a Dim statement in the VB Code something like
Dim intCount as Interger

Change that to read:
Dim intCount as long


bennyboy said:
to all who are trying to help me, i did select a data range en named it
Data, the nex thing that i did was within that range a empty cell en
placed there =Today() and named that cell today

then i proceded to the VB and pasted the example into it, when i try to
run the VB script it return a error 6 overflow

debug points to the


intCount = MyRange.Count

please help me out with this one

Thanks


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to
Click to expand...
creating financial statements
 
he he it takes a wile before we get a working thing i guess,what i hav
now in VB returns error 1004 error Method Range of object _Globa
Failed

This is what i have now in VB

Public Sub FindDate()
Dim intCount As Long
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 Su
 
Back
Top