Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Go to Today's Date
Hi,
I have a home budget data list with col B containing past and future dates (dd/mm/yy) for income and expenditure / bills that appear in adjacent cells. These dates for expenses/expenditure are entered randomly, though sorted on date often. I'm struggling to find code to attach to a button that will go down the list (B4:B2000) so today's date is displayed, or if today's date isn't entered, the closest date to today. Entries will always appear at least every fortnight (pay day). Any help would be appreciated. Regards, Ricky |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Go to Today's Date
Thanks Garry.
I'm not really keen to use the Group option as I use Excel 2003 at home (but use Excel 2007 at work). Yes, I suppose I can always save the worksheet at today's date, though a macro is preferable to move around my data quickly. I've found this bit of code, though it fails if I have not got any entry dated today's date in my list - though I might have a date close to it, and ceratinly with 13 days of it. Can this be modified at all to go the the closest date before today's date? Regards Ricky (via spencer's account) Sub Find_Todays_Date() '--If you have date's in column B then this example will select the cell with today's date. Dim FindString As Date Dim Rng As Range FindString = CLng(Date) With Sheets("Daily").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 "That date is not entered" End If End With Range("A4").Select End Sub On Aug 17, 10:23*am, GS wrote: expressed precisely : Hi, I have a home budget data list with col B containing past and future dates (dd/mm/yy) for income and expenditure / bills that appear in adjacent cells. These dates for expenses/expenditure are entered randomly, though sorted on date often. I'm struggling to find code to attach to a button that will go down the list (B4:B2000) so today's date is displayed, or if today's date isn't entered, the closest date to today. Entries will always appear at least every fortnight (pay day). Any help would be appreciated. Regards, Ricky IMO, it would be easier to save then close the workbook at the last entry position. Also, you could 'randomly' enter transactions in the same area as other dated transactions. Using Group..rows by month would give you individual 'sections' for each month. To have your month sections expand/collapse you need to remove the check in the Settings dialog to place summary rows below the data. This results as follows: Jan * Jan entry * Jan entry * Jan entry * ... Feb * Feb entry * Feb entry * Feb entry * ... ..with the expand/collapse buttons in a margin to the left of the row headers. I'd use a larger row height for the month headings so they 'appear' separated by some space. This, then, will allow you to leave the current month expanded and only expand other months as needed. Also, each month section could be sorted independently to order your entries chronologically, OR you could insert rows where needed for new entries. -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Go to Today's Date
Modified the code to repeat up to 15 times.
After each try, if the date is not found, the date is reduced by one day and the code repeats. '--- Sub Find_Todays_Date_R1() Dim FindString As Date Dim Rng As Range Dim N As Long FindString = CLng(Date) For N = 1 To 15 With Sheets("Daily").Range("B:B") Set Rng = .Find(What:=FindString, _ After:=.Cells(.Cells.Count), _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) End With If Not Rng Is Nothing Then Application.Goto Rng, True Exit Sub Else FindString = FindString - 1 End If Next MsgBox "That date is not entered" Range("A4").Select End Sub '--- Jim Cone Portland, Oregon USA . http://www.mediafire.com/PrimitiveSoftware . (Data Rows add-in: Custom Shading, Deleting, Inserting) "Ricky" wrote in message ... Thanks Garry. I'm not really keen to use the Group option as I use Excel 2003 at home (but use Excel 2007 at work). Yes, I suppose I can always save the worksheet at today's date, though a macro is preferable to move around my data quickly. I've found this bit of code, though it fails if I have not got any entry dated today's date in my list - though I might have a date close to it, and ceratinly with 13 days of it. Can this be modified at all to go the the closest date before today's date? Regards Ricky (via spencer's account) Sub Find_Todays_Date() '--If you have date's in column B then this example will select the cell with today's date. Dim FindString As Date Dim Rng As Range FindString = CLng(Date) With Sheets("Daily").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 "That date is not entered" End If End With Range("A4").Select End Sub On Aug 17, 10:23 am, GS wrote: expressed precisely : Hi, I have a home budget data list with col B containing past and future dates (dd/mm/yy) for income and expenditure / bills that appear in adjacent cells. These dates for expenses/expenditure are entered randomly, though sorted on date often. I'm struggling to find code to attach to a button that will go down the list (B4:B2000) so today's date is displayed, or if today's date isn't entered, the closest date to today. Entries will always appear at least every fortnight (pay day). Any help would be appreciated. Regards, Ricky IMO, it would be easier to save then close the workbook at the last entry position. Also, you could 'randomly' enter transactions in the same area as other dated transactions. Using Group..rows by month would give you individual 'sections' for each month. To have your month sections expand/collapse you need to remove the check in the Settings dialog to place summary rows below the data. This results as follows: Jan Jan entry Jan entry Jan entry ... Feb Feb entry Feb entry Feb entry ... ..with the expand/collapse buttons in a margin to the left of the row headers. I'd use a larger row height for the month headings so they 'appear' separated by some space. This, then, will allow you to leave the current month expanded and only expand other months as needed. Also, each month section could be sorted independently to order your entries chronologically, OR you could insert rows where needed for new entries. -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Go to Today's Date
Perfect! Thanks Jim, much appreciated.
Regards, Ricky On Aug 17, 7:40*pm, "Jim Cone" wrote: Modified the code to repeat up to 15 times. After each try, if the date is not found, the date is reduced by one day and the code repeats. '--- Sub Find_Todays_Date_R1() Dim FindString As Date Dim Rng As Range Dim N As Long FindString = CLng(Date) For N = 1 To 15 With Sheets("Daily").Range("B:B") * Set Rng = .Find(What:=FindString, _ * After:=.Cells(.Cells.Count), _ * LookIn:=xlFormulas, _ * LookAt:=xlWhole, _ * SearchOrder:=xlByRows, _ * SearchDirection:=xlNext, _ * MatchCase:=False) End With If Not Rng Is Nothing Then * Application.Goto Rng, True * Exit Sub Else * FindString = FindString - 1 End If Next MsgBox "That date is not entered" Range("A4").Select End Sub '--- Jim Cone Portland, Oregon USA *.http://www.mediafire.com/PrimitiveSoftware*. (Data Rows add-in: Custom Shading, Deleting, Inserting) "Ricky" wrote in ... Thanks Garry. I'm not really keen to use the Group option as I use Excel 2003 at home (but use Excel 2007 at work). *Yes, I suppose I can always save the worksheet at today's date, though a macro is preferable to move around my data quickly. I've found this bit of code, though it fails if I have not got any entry dated today's date in my list - though I might have a date close to it, and ceratinly with 13 days of it. Can this be modified at all to go the the closest date before today's date? Regards Ricky (via spencer's account) Sub Find_Todays_Date() '--If you have date's in column B then this example will select the cell with today's date. Dim FindString As Date Dim Rng As Range FindString = CLng(Date) With Sheets("Daily").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 "That date is not entered" End If End With Range("A4").Select End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditional formatting for cell date to equal today's date | Excel Worksheet Functions | |||
date in Cell to change colors if the date is beyond today's date | Excel Discussion (Misc queries) | |||
I need today's date returned as date format in formula | Excel Discussion (Misc queries) | |||
Count number of cells with date <today's date | New Users to Excel | |||
count the number of cells with a date <= today's date | New Users to Excel |