Help with macro

9 views
Skip to first unread message

work again

unread,
Jul 27, 2001, 4:41:52 AM7/27/01
to
Hi all

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


Tom Ogilvy

unread,
Jul 27, 2001, 7:16:03 AM7/27/01
to
Call the below code from your workbook_Open event and it should get you
started. It assumes all dates are present and in column A. If you skip
dates or the dates include time, then you might want to change the third
argument of match to 1 instead of 0.


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...

David McRitchie

unread,
Jul 27, 2001, 7:19:35 AM7/27/01
to
Hi Paul, (posted without email copy)
Don't know if this is what you mean, the following will
look for today's date in Column B and scroll down to it.
If you meant to filter the dates it would be hard to update,
other information when filtered so will pass this one by you first.

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...

David McRitchie

unread,
Jul 27, 2001, 10:59:13 AM7/27/01
to
Since I still don't see my reply in the newsgroup, I will repost since the
posters email address looks obviously invalid, sorry if it turns out
to be a duplicate. Tom's answer may include better treatment
for dates both in US and out of US, I don't know. Mine is tested
in US. only. But I don't see Tom's reply as automatically
being invoked so here is a repost (at least if duplicated it will
be in the same thread). David

----------------------------------------------

work again

unread,
Jul 30, 2001, 3:42:09 AM7/30/01
to
Many thanks to Tom and Dave for their help.

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...

work again

unread,
Jul 30, 2001, 4:42:47 AM7/30/01
to
Well Friday must have been a long day, Tom managed to get the macro working,
bit of an oversight, changed the debug lin eto match the worksheet and
voila, one quick question I had to delete the top 2 lines (these contained
titles etc and no dates) before it would select the range, is there a way
around this.

cheers

Paul
"work again" <123...@uku.co.uk> wrote in message
news:3b612987$1...@mk-nntp-1.news.uk.worldonline.com...

work again

unread,
Jul 30, 2001, 7:41:21 AM7/30/01
to
Tom

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...

Tom Ogilvy

unread,
Jul 30, 2001, 8:06:31 AM7/30/01
to
Cells(1,1) refers to Cells(row:=1,Column:=1)

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...

Reply all
Reply to author
Forward
0 new messages