Hi PatWith the headers in row 1 of "Sheet1" try this oneSubFind_Date_test()
This works perfectly if the date is keyed as an actual date. However, my dates are calculated based on the first date in the row (i.e., =NK5+7).
If I display the date using MsgBox, it looks like a date. If I compare Range("nu5") = CLng(Date) it returns True. But when I run the macro, it displays "Nothing found." I'm confused. Thanks for your help... MPV On Monday, January 14, 2008 2:06 PM Pat Hawkins wrote: I need an Excel Macro that will select the current date from a column of dates. This macro needs to run automatically when the worksheet is opened. If anybody has any tips on how to do this, I would greatly appreciate it! Thanks in advance Pat On Monday, January 14, 2008 2:19 PM Ron de Bruin wrote: Try this macro If you have date's in column A then this example will select the cell with today's date. Sub Find_Todays_Date() Dim FindString As Date Dim Rng As Range FindString = CLng(Date) With Sheets("Sheet1").Range("A:A") Set Rng = .Find(What:=FindString, _ After:=.Cells(.Cells.Count), _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not Rng Is Nothing Then Application.Goto Rng, True Else MsgBox "Nothing found" End If End With End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pat Hawkins" wrote in message ... On Monday, January 14, 2008 3:50 PM Pat Hawkins wrote: Ron Thanks for your quick response. One more question. In the spreadsheet I am working on there are headings for each month. Each month is entered as 'mm/01/yyyy', but formatted to display as 'mmmm-yy'. As an example, the January heading is entered as 01/01/2008 but is formatted as a custom date to display as 'January-08'. How can I modify this macro, to go to the heading for the current month? Thanks again. Pat "Ron de Bruin" wrote in message ... On Monday, January 14, 2008 4:02 PM Ron de Bruin wrote: Hi Pat With the headers in row 1 of "Sheet1" try this one Sub Find_Date_test() Dim FindString As Date Dim Rng As Range FindString = CLng(DateSerial(Year(Date), Month(Date), 1)) With Sheets("Sheet1").Rows("1:1") Set Rng = .Find(What:=FindString, _ After:=.Cells(.Cells.Count), _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not Rng Is Nothing Then Application.Goto Rng, True Else MsgBox "Nothing found" End If End With End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pat Hawkins" wrote in message ... On Monday, January 14, 2008 6:08 PM Pat Hawkins wrote: Thanks for all your help. That worked! Pat On Tuesday, April 27, 2010 7:40 PM James Uzy wrote: Hi and thanks for the post as this works witn a single sheet. I am trying to get excel to go to the date on multiple sheets when the workbook opens. The code below allows this to happen but on one sheet only. Can someone please advise how to change this to include multiple sheets (there are only 4). Very new to VBA and these forums have been gold. thanks Submitted via EggHeadCafe C# In Depth Second Edition - An Interview with Jon Skeet http://www.eggheadcafe.com/tutorials...jon-skeet.aspx |
All times are GMT +1. The time now is 03:26 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com