sorry - having a bit of a mind-block. I have a worksheet with a column of
dates and would like to auto-scroll to the current date (if available) in
column B, every time the sheet is activated (so that the row is visible for
entering fresh data)... i know i should be able to do this, but just can't
think how... its been a long day! Can anyone give me the answer?

tia,

tim

Option Explicit

Private Sub Worksheet_Activate()

Cells.Find(Date).Select

End Sub

Hope this helps,
Amit

"Tim Marsh" wrote in message
...
sorry - having a bit of a mind-block. I have a worksheet with a column of
dates and would like to auto-scroll to the current date (if available) in
column B, every time the sheet is activated (so that the row is visible
for
entering fresh data)... i know i should be able to do this, but just can't
think how... its been a long day! Can anyone give me the answer?

tia,

tim

Sorry missed the scrolling automatically part, so please use this instead :

Option Explicit

Private Sub Worksheet_Activate()

Cells.Find(Date).Select
ActiveWindow.ScrollRow = ActiveCell.Row

End Sub

Amit
"Amit Shanker" wrote in message
...
Option Explicit

Private Sub Worksheet_Activate()

Cells.Find(Date).Select

End Sub

Hope this helps,
Amit

"Tim Marsh" wrote in message
...
sorry - having a bit of a mind-block. I have a worksheet with a column
of
dates and would like to auto-scroll to the current date (if available)
in
column B, every time the sheet is activated (so that the row is visible
for
entering fresh data)... i know i should be able to do this, but just
can't
think how... its been a long day! Can anyone give me the answer?

tia,

tim

Use the sheet activate event:

Dim rng as Range
set rng = me.Range(me.Cells(1,1),me.Cells(me.rows.count,1).E nd(xlup))
res = Application.Match(clng(date),rng,0)
if not iserror(res) then
rng(res).Select
End if

--
Regards,
Tom Ogilvy


"Tim Marsh" wrote in message
...
sorry - having a bit of a mind-block. I have a worksheet with a column of
dates and would like to auto-scroll to the current date (if available) in
column B, every time the sheet is activated (so that the row is visible
for
entering fresh data)... i know i should be able to do this, but just can't
think how... its been a long day! Can anyone give me the answer?

tia,

tim

Thank you Amit (& Tom)

this'll do nicely... for the record i did fine yet another way, but it
involved a 'do...until' loop and was very inefficient.

cheers from foggy, wet England,

tim

"Amit Shanker" wrote in message
...
Sorry missed the scrolling automatically part, so please use this instead
:

Option Explicit

Private Sub Worksheet_Activate()

Cells.Find(Date).Select
ActiveWindow.ScrollRow = ActiveCell.Row

End Sub

Amit
"Amit Shanker" wrote in message
...
Option Explicit

Private Sub Worksheet_Activate()

Cells.Find(Date).Select

End Sub

Hope this helps,
Amit

"Tim Marsh" wrote in message
...
sorry - having a bit of a mind-block. I have a worksheet with a
column
of
dates and would like to auto-scroll to the current date (if available)
in
column B, every time the sheet is activated (so that the row is
visible
for
entering fresh data)... i know i should be able to do this, but just
can't
think how... its been a long day! Can anyone give me the answer?

tia,

tim

Note that in most versions of Excel Cell.Find(Date) doesn't work very
reliably.

--
Regards,
Tom Ogilvy

"Amit Shanker" wrote in message
...
Sorry missed the scrolling automatically part, so please use this instead
:

Option Explicit

Private Sub Worksheet_Activate()

Cells.Find(Date).Select
ActiveWindow.ScrollRow = ActiveCell.Row

End Sub

Amit
"Amit Shanker" wrote in message
...
Option Explicit

Private Sub Worksheet_Activate()

Cells.Find(Date).Select

End Sub

Hope this helps,
Amit

"Tim Marsh" wrote in message
...
sorry - having a bit of a mind-block. I have a worksheet with a
column
of
dates and would like to auto-scroll to the current date (if available)
in
column B, every time the sheet is activated (so that the row is
visible
for
entering fresh data)... i know i should be able to do this, but just
can't
think how... its been a long day! Can anyone give me the answer?

tia,

tim

Tom, any idea why ? Would like to understand this better.

Thanks,
Amit


"Tom Ogilvy" wrote in message
...
Note that in most versions of Excel Cell.Find(Date) doesn't work very
reliably.

--
Regards,
Tom Ogilvy

"Amit Shanker" wrote in message
...
Sorry missed the scrolling automatically part, so please use this
instead
:

Option Explicit

Private Sub Worksheet_Activate()

Cells.Find(Date).Select
ActiveWindow.ScrollRow = ActiveCell.Row

End Sub

Amit
"Amit Shanker" wrote in message
...
Option Explicit

Private Sub Worksheet_Activate()

Cells.Find(Date).Select

End Sub

Hope this helps,
Amit

"Tim Marsh" wrote in message
...
sorry - having a bit of a mind-block. I have a worksheet with a
column
of
dates and would like to auto-scroll to the current date (if
available)
in
column B, every time the sheet is activated (so that the row is
visible
for
entering fresh data)... i know i should be able to do this, but just
can't
think how... its been a long day! Can anyone give me the answer?

tia,

tim

I can't say why - I don't have the code for the Find method. I just know
that in my experience, match as I show, is much more reliable (never seen it
fail if there is a match - but even with that, I have to convert to long for
the search). On the other hand, I have plenty of problems with Find not
finding dates when they exist.

Some have suggest that for the lookin parameter xlValues works while others
say xlformulas work. I haven't found a consistent solution (and I imagine
this would be affected by how the value in the cell is produced). Tim
doesn't set any parameters, so such usage is a crap shoot as to what the
last setting the user applied was/is.

On the other hand, I work mostly in xl97 and xl2000, so it may not be a
problem for you.

--
Regards,
Tom Ogilvy



"Amit Shanker" wrote in message
...
Tom, any idea why ? Would like to understand this better.

Thanks,
Amit


"Tom Ogilvy" wrote in message
...
Note that in most versions of Excel Cell.Find(Date) doesn't work very
reliably.

--
Regards,
Tom Ogilvy

"Amit Shanker" wrote in message
...
Sorry missed the scrolling automatically part, so please use this
instead
:

Option Explicit

Private Sub Worksheet_Activate()

Cells.Find(Date).Select
ActiveWindow.ScrollRow = ActiveCell.Row

End Sub

Amit
"Amit Shanker" wrote in message
...
Option Explicit

Private Sub Worksheet_Activate()

Cells.Find(Date).Select

End Sub

Hope this helps,
Amit

"Tim Marsh" wrote in message
...
sorry - having a bit of a mind-block. I have a worksheet with a
column
of
dates and would like to auto-scroll to the current date (if
available)
in
column B, every time the sheet is activated (so that the row is
visible
for
entering fresh data)... i know i should be able to do this, but
just
can't
think how... its been a long day! Can anyone give me the answer?

tia,

tim

OK thanks for the explanation, Tom; appreciate it. I am using Office XP so
thankfully this has not been a problem, but when providing solutions for
previous versions of Excel, definitely something to keep in mind.

Amit


"Tom Ogilvy" wrote in message
...
I can't say why - I don't have the code for the Find method. I just know
that in my experience, match as I show, is much more reliable (never seen
it
fail if there is a match - but even with that, I have to convert to long
for
the search). On the other hand, I have plenty of problems with Find not
finding dates when they exist.

Some have suggest that for the lookin parameter xlValues works while
others
say xlformulas work. I haven't found a consistent solution (and I imagine
this would be affected by how the value in the cell is produced). Tim
doesn't set any parameters, so such usage is a crap shoot as to what the
last setting the user applied was/is.

On the other hand, I work mostly in xl97 and xl2000, so it may not be a
problem for you.

--
Regards,
Tom Ogilvy



"Amit Shanker" wrote in message
...
Tom, any idea why ? Would like to understand this better.

Thanks,
Amit


"Tom Ogilvy" wrote in message
...
Note that in most versions of Excel Cell.Find(Date) doesn't work
very
reliably.

--
Regards,
Tom Ogilvy

"Amit Shanker" wrote in message
...
Sorry missed the scrolling automatically part, so please use this
instead
:

Option Explicit

Private Sub Worksheet_Activate()

Cells.Find(Date).Select
ActiveWindow.ScrollRow = ActiveCell.Row

End Sub

Amit
"Amit Shanker" wrote in message
...
Option Explicit

Private Sub Worksheet_Activate()

Cells.Find(Date).Select

End Sub

Hope this helps,
Amit

"Tim Marsh" wrote in message
...
sorry - having a bit of a mind-block. I have a worksheet with a
column
of
dates and would like to auto-scroll to the current date (if
available)
in
column B, every time the sheet is activated (so that the row is
visible
for
entering fresh data)... i know i should be able to do this, but
just
can't
think how... its been a long day! Can anyone give me the
answer?

tia,

tim

Speculative...might it have something to do with the difference between
Value and Value2? If A1 contains a date (1/1/2003), then
MsgBox TypeName([a1].Value) & ", " & TypeName([a1].Value2)
returns Date, Double.

In the code below, the 2nd Find faults:

Sub testIt2()
Dim x As Date, y As Long
x = #1/1/2003#: y = x
MsgBox x & ", " & y
MsgBox Cells.Find(x).Address
MsgBox Cells.Find(y).Address
End Sub

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
I can't say why - I don't have the code for the Find method. I just know
that in my experience, match as I show, is much more reliable (never seen it
fail if there is a match - but even with that, I have to convert to long for
the search). On the other hand, I have plenty of problems with Find not
finding dates when they exist.

Some have suggest that for the lookin parameter xlValues works while others
say xlformulas work. I haven't found a consistent solution (and I imagine
this would be affected by how the value in the cell is produced). Tim
doesn't set any parameters, so such usage is a crap shoot as to what the
last setting the user applied was/is.

On the other hand, I work mostly in xl97 and xl2000, so it may not be a
problem for you.