If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 . |
#2
|
|||
|
|||
finding the date in a sheet
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 . . |
Thread Tools | |
Display Modes | |
|
|