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

VBA Go to Today's Date
 

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 at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

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

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

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