Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Open macro go to beginning of week
I have a spreadsheet where I have people enter data each day. The
spreadsheet has each day in column B and the associated data for that date in the adjacent columns. There is a macro so that the spreadsheet automatically opens to today's date using the code below. My question is, if I change the dates in column B to be the first day of the week (using the Sunday standard) and have people enter data once a week, how can I change the code below to find the date of the first day of this week? Thanks, Joe Macro code: Sub Auto_Open() ' ' Auto_Open Macro Dim FindString As Date Dim Rng As Range FindString = CLng(Date) With Sheets("Sheet Name Here").Range("B:B") Set Rng = .Find(What:=FindString, _ After:=.Cells(.Cells.Count), _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not Rng Is Nothing Then Application.Goto Rng, True Else MsgBox "Today's Date Not Found" End If End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Open macro go to beginning of week
FindString = CLng(Date - Weekday(Date) + 1)
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Joseph Rosing" wrote in message ... I have a spreadsheet where I have people enter data each day. The spreadsheet has each day in column B and the associated data for that date in the adjacent columns. There is a macro so that the spreadsheet automatically opens to today's date using the code below. My question is, if I change the dates in column B to be the first day of the week (using the Sunday standard) and have people enter data once a week, how can I change the code below to find the date of the first day of this week? Thanks, Joe Macro code: Sub Auto_Open() ' ' Auto_Open Macro Dim FindString As Date Dim Rng As Range FindString = CLng(Date) With Sheets("Sheet Name Here").Range("B:B") Set Rng = .Find(What:=FindString, _ After:=.Cells(.Cells.Count), _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not Rng Is Nothing Then Application.Goto Rng, True Else MsgBox "Today's Date Not Found" End If End With End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Open macro go to beginning of week
On May 19, 4:03*pm, "Bob Phillips" wrote:
* * FindString = CLng(Date - Weekday(Date) + 1) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "JosephRosing" wrote in message ... I have a spreadsheet where I have people enter data each day. *The spreadsheet has each day in column B and the associated data for that date in the adjacent columns. *There is a macro so that the spreadsheet automatically opens to today's date using the code below. My question is, if I change the dates in column B to be the first day of theweek(using the Sunday standard) and have people enter data once aweek, how can I change the code below tofindthe date of the first day of thisweek? Thanks, Joe Macro code: Sub Auto_Open() ' ' Auto_Open Macro * *Dim FindString As Date * *Dim Rng As Range * *FindString = CLng(Date) * *With Sheets("Sheet Name Here").Range("B:B") * * * *Set Rng = .Find(What:=FindString, _ * * * * * * * * * * * *After:=.Cells(.Cells.Count), _ * * * * * * * * * * * *LookIn:=xlFormulas, _ * * * * * * * * * * * *LookAt:=xlWhole, _ * * * * * * * * * * * *SearchOrder:=xlByRows, _ * * * * * * * * * * * *SearchDirection:=xlNext, _ * * * * * * * * * * * *MatchCase:=False) * * * *If Not Rng Is Nothing Then * * * * * *Application.Goto Rng, True * * * *Else * * * * * *MsgBox "Today's Date Not Found" * * * *End If * *End With End Sub- Hide quoted text - - Show quoted text - Thanks for the reply. Now a slight change... I want to input the data using Friday as the first day of the week. I have changed the code to find Friday's date [FindString = CLng(Date - Weekday(Date) + 6)] but if I open the spreadsheet on any day but Friday it gives me the "Today's Date Not Found" message. How can I script this so that it finds the date today (if today is Friday) or the previous Friday? Thanks for the help! Here's the adapted code: Sub Todays_Date() Dim FindString As Date Dim Rng As Range FindString = CLng(Date - Weekday(Date) + 6) With Sheets("Sheet Name Here").Range("C:C") Set Rng = .Find(What:=FindString, _ After:=.Cells(.Cells.Count), _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not Rng Is Nothing Then Application.Goto Rng, True Else MsgBox "Today's Date Not Found" End If End With End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert regular Date to Week Ending or Week Beginning Dates | Excel Discussion (Misc queries) | |||
Auto calculate day of week to week of the year (not as serial) | Excel Discussion (Misc queries) | |||
how do i have beginning balance take away an amount auto total | Excel Worksheet Functions | |||
open up an excel program with an auto open macro | Excel Programming | |||
Rouding Dates to beginning of a week | Excel Discussion (Misc queries) |