Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scrolling and date matching
I have a holiday planner leave sheet in Excel for 2009 /2010 with the
top row being the dates of the days of the “weekday” entered. I also have one cell with =Today() in it, I need a macro to Auto_open and scroll the sheet along to the same date as Today. Or a Macro where I can enter the week beginning date, e.g 17/8/09 and the sheet to auto scroll across to that date. Ta guys and gals |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scrolling and date matching
Hi Johnny,
The following code will place the cell with =Today() in the top left of the window. To install the code. (Not sure if you know how so just in case) Alt/F11 to open the VBA editor. On the left of the screen the Project explorer should be displayed with the Sheet modules and one named ThisWorkbook. (If you can't see the Project Explorer the Ctrl/r will display it. Double Click ThisWorkbook. Copy and paste the code into the large white area. Edit the worksheet name towards the top of the code where the comment is. Click the X in the red background top right to close the VBA editor. Save the workbook. (If xl2007 ensure you save as Excel Enabled workbook.) Private Sub Workbook_Open() Dim rngRow1 As Range Dim rngToFind As Range Dim dateToday As Date 'Edit Sheet1 to match your worksheet Sheets("Sheet1").Select With ActiveSheet Set rngRow1 = .Rows(1) End With dateToday = Date With rngRow1 Set rngToFind = .Find(What:=dateToday, _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False) End With If Not rngToFind Is Nothing Then rngToFind.Select ActiveWindow.ScrollRow = rngToFind.Row ActiveWindow.ScrollColumn = rngToFind.Column Else MsgBox "Date " & dateToday & " not found" End If End Sub -- Regards, OssieMac |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scrolling and date matching
Hi
I dont know which cell you have todays date in in thi sub i have set it as B2 and assumed that your dates are all accross row 1. With Worksheets("sheet1").Range("1:1") Set c = .Find(Range("B2").Value, LookIn:=xlValues) If Not c Is Nothing Then Application.Goto reference:=Range(c.Address), Scroll:=True End If End With good luck John |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scrolling and date matching
I should have said that I assumed from your request that Today() is in row 1
and that is where the code is looking for it. If not in row 1 then edit the following line and change the .Row(1) to match the row where Today() is. Set rngRow1 = .Rows(1) -- Regards, OssieMac |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scrolling and date matching
On 8 Aug, 13:05, OssieMac wrote:
I should have said that I assumed from your request that Today() is in row 1 and that is where the code is looking for it. If not in row 1 then edit the following line and change the .Row(1) to match the row where Today() is. Set rngRow1 = .Rows(1) -- Regards, OssieMac Thanks you guys, will give it a try and get back to this post with my results Johnnyboy |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scrolling and date matching
On 8 Aug, 13:35, Johnnyboy5 wrote:
On 8 Aug, 13:05, OssieMac wrote: I should have said that I assumed from your request that Today() is in row 1 and that is where the code is looking for it. If not in row 1 then edit the following line and change the .Row(1) to match the row where Today() is.. Set rngRow1 = .Rows(1) -- Regards, OssieMac Thanks you guys, *will give it a try and get back to this post with my results Johnnyboy Thanks Ossie, works a treat, also sent a reply to author in this post... I think |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scrolling and date matching
Your Range("B2") and Range(c.Address) references will refer to the ActiveSheet which is not guaranteed to be (your example) "sheet1". Perhaps this would be a better way to rearrange your code so that all references can be dotted in order to refer back to the same worksheet...
Dim c As Range ...... ...... With Worksheets("sheet1") Set c = .Range("1:1").Find(.Range("B2").Value, LookIn:=xlValues) If Not c Is Nothing Then Application.Goto reference:=.Range(c.Address), Scroll:=True End If End With -- Rick (MVP - Excel) "Atishoo" wrote in message ... Hi I dont know which cell you have todays date in in thi sub i have set it as B2 and assumed that your dates are all accross row 1. With Worksheets("sheet1").Range("1:1") Set c = .Find(Range("B2").Value, LookIn:=xlValues) If Not c Is Nothing Then Application.Goto reference:=Range(c.Address), Scroll:=True End If End With good luck John |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scrolling and date matching
Indeed it would! fortunately ossie posted a far superior code (am just
playing with that at the moment) am looking to adapt it to make a rolling page that scrolls across a 24 hour period like a linear clock using similar sub with time rather than dates! "Rick Rothstein" wrote: Your Range("B2") and Range(c.Address) references will refer to the ActiveSheet which is not guaranteed to be (your example) "sheet1". Perhaps this would be a better way to rearrange your code so that all references can be dotted in order to refer back to the same worksheet... Dim c As Range ...... ...... With Worksheets("sheet1") Set c = .Range("1:1").Find(.Range("B2").Value, LookIn:=xlValues) If Not c Is Nothing Then Application.Goto reference:=.Range(c.Address), Scroll:=True End If End With -- Rick (MVP - Excel) "Atishoo" wrote in message ... Hi I dont know which cell you have todays date in in thi sub i have set it as B2 and assumed that your dates are all accross row 1. With Worksheets("sheet1").Range("1:1") Set c = .Find(Range("B2").Value, LookIn:=xlValues) If Not c Is Nothing Then Application.Goto reference:=Range(c.Address), Scroll:=True End If End With good luck John |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scrolling and date matching
hi johnnyboy you might want to click ossies post as being useful! its a great little piece of code hes posted there. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scrolling and date matching
On 8 Aug, 15:35, Atishoo wrote:
hi johnnyboy you might want to click ossies post as being useful! its a great little piece of code hes posted there. Yer your right, have just done 5 star ! JB |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scrolling and date matching
On 8 Aug, 17:05, Johnnyboy5 wrote:
On 8 Aug, 15:35, Atishoo wrote: hi johnnyboy you might want to click ossies post as being useful! its a great little piece of code hes posted there. Yer your right, *have just done * 5 star ! JB Hi just one more question, how do I set it it to find the date to be 7 days early than the current date? many thanks Ossie. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scrolling and date matching
On 8 Aug, 22:41, Johnnyboy5 wrote:
On 8 Aug, 17:05, Johnnyboy5 wrote: On 8 Aug, 15:35, Atishoo wrote: hi johnnyboy you might want to click ossies post as being useful! its a great little piece of code hes posted there. Yer your right, *have just done * 5 star ! JB Hi just one more question, *how do I set it it to find the date to be 7 days early than the current date? many thanks *Ossie. Hey no worries sorted it Date () - 7 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel- smooth scrolling (instead of 'snap' scrolling) | Setting up and Configuration of Excel | |||
Help me to first row matching First date and last row matching lastrow | Excel Programming | |||
Vertical scrolling...jumps rather than smooth scrolling | Excel Discussion (Misc queries) | |||
Live Scrolling/Real-Time /Smooth Scrolling doesn't work for me in Excel, even 2007 beta | Excel Discussion (Misc queries) | |||
Live Scrolling/Real-Time /Smooth Scrolling doesn't work for me in Excel 2003 | Excel Discussion (Misc queries) |