scrolling to a specific cell

  • Thread starter Thread starter Tim Marsh
  • Start date Start date
T

Tim Marsh

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
 
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
 
Use the sheet activate event:

Dim rng as Range
set rng = me.Range(me.Cells(1,1),me.Cells(me.rows.count,1).End(xlup))
res = Application.Match(clng(date),rng,0)
if not iserror(res) then
rng(res).Select
End if
 
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
 
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.
 
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
 
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
 
Back
Top