VBA Go to Today's Date

[spencer]

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

 

[GS]

(e-mail address removed) 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.

 

[Ricky]

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

 

[Jim Cone]

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" <[email protected]>
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

 

[Ricky]

Perfect! Thanks Jim, much appreciated.

Regards,
Ricky