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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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. |
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 |
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. |
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 |
All times are GMT +1. The time now is 12:33 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com