Yet again I find myself on the newsgroup asking for your help.
I have an excel spreadsheet that contains all of the shifts for support.
ie
Column A = Day
Column B = Date
Column C = User1
Column D - User2
etc etc
What I am trying to achieve with the macro is that whenever the spreadsheet
is opened it makes the current 4 weeks active and instantly viewable.
I thought about using =now() in A1 and then trying to do something with
find, but I am getting confused with how to select the next 4 weeks and show
them on the screen.
Any help is appreciated, TIA
Paul
Sub Tester1()
Dim bNotFound As Boolean
Dim rng As Range
Dim StartRow As Range
Dim EndRow As Range
Dim res As Variant
Worksheets("Dates").Activate
bNotFound = False
Set rng = Range(Cells(1, 1), Cells(1, 1).End(xlDown))
res = Application.Match(CLng(Date), rng, 0)
If Not IsError(res) Then
Set StartRow = rng(res)
Else
bNotFound = True
End If
res = ""
res = Application.Match(CLng(Date + 28), rng, 0)
If Not IsError(res) Then
Set EndRow = rng(res)
Else
bNotFound = True
End If
Range(StartRow, EndRow).Select
ActiveWindow.Zoom = True
ActiveWindow.ScrollRow = StartRow.Row
StartRow.Select
End Sub
Regards,
Tom Ogilvy
"work again" <123...@uku.co.uk> wrote in message
news:3b612987$1...@mk-nntp-1.news.uk.worldonline.com...
Option Explicit
Sub FindTodayIn_Col_B()
Columns("B:B").Select
Selection.Find(What:=Date, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
Application.Goto Reference:=Cells(ActiveCell.Row, 1), Scroll:=True
ActiveCell.Offset(0, 1).Activate 'make date the selected cell
End Sub.
Invoke the above from a Sheet Event.
Unlike regular macros which are installed in regular modules, Worksheet
Events are installed with the worksheet by rightclicking on the sheettab,
choose 'view code', and then paste in your macro.
Option Explicit
Private Sub Worksheet_Activate()
Call FindTodayIn_Col_B
End Sub
HTH, (more information in: buildtoc.htm, event.htm)
A more descriptive subject title would be helpful, this group is macros
and adds no meaning to people looking through archived postings.
David McRitchie, Microsoft MVP - Excel
My Excel Macros: http://www.geocities.com/davemcritchie/excel/excel.htm
"work again" <123...@uku.co.uk> wrote in message news:3b612987$1...@mk-nntp-1.news.uk.worldonline.com...
----------------------------------------------
Dave I have used your macro as it seemed to work first off without any
modification, Tom I am having a few problems with your macro but it is
probably down to me not knowing too much about macros, it reports a runtime
error 9, subscript out of range this is for line
worksheets("dates").activate, I haven't given up yet though and will see
if I can figure out what the problem is.
Thanks for your help
Paul
"work again" <123...@uku.co.uk> wrote in message
news:3b612987$1...@mk-nntp-1.news.uk.worldonline.com...
cheers
Paul
"work again" <123...@uku.co.uk> wrote in message
news:3b612987$1...@mk-nntp-1.news.uk.worldonline.com...
Ignore all of my last comments, with Friday being a long day and sunstroke
setting in on Monday, I have now managed to make your macro work, with a
minor modification, I changed the Set rng = Range to be a named range, which
then removed the need to remove the top 2 rows.
again thanks for your help
Paul
"work again" <123...@uku.co.uk> wrote in message
news:3b612987$1...@mk-nntp-1.news.uk.worldonline.com...
Change Cells(1,1) to Cells(3,1) if you want to start from A3.
However, there is nothing in the code that would cause it to fail given the
existing construct unless cell A2 is blank.
Yes, when testing my code, I named my sheet "Dates". If your sheet has a
different name, then it would be appropriate to use that - if no Dates sheet
exists and the code remains Worksheets("Dates").Activate, it will give a
Subscript out of Range error - error 9.
Regards,
Tom Ogilvy
"work again" <123...@uku.co.uk> wrote in message
news:3b651e37$1...@mk-nntp-1.news.uk.worldonline.com...