Find today's date in Excel Worksheet

[CZ]

Hello everyone

Been trying to figure out how to find today's date in a cell in an Excel Worksheet and have that cell selected. I've done some research and I'm still thoroughly confused. The only thing that I'm pretty certain of is that I would have to use VBScript. I am not very good with it though. So far this is all I could come up with. I would appreciate any help.

VBSTART

Sub xlExample

Set oExcel = CreateObject("Excel.Application")
oExcel.Visible = true
oExcel.Workbooks.Open "C:\My Stuff\Data\microsoft excel 97\BTB.xlsx"
oExcel.Sheets("BTB-Workout").Select
oExcel.Range("C2:XFD1000").Select
oExcel.Range.Find(date())

End Sub

VBEND

VBRun>xlExample

I keep getting an error on the " oExcel.Range.Find(date())" line, but i can't figure how to fix it. Again I would appreciate any help.
Thank you

[hagchr]

Hi, hopefully this will help you.

Code: Select all

VBSTART
Sub xlExample(date)
Set oExcel = CreateObject("Excel.Application")
oExcel.Visible = true
oExcel.Workbooks.Open "C:\My Stuff\Data\microsoft excel 97\BTB.xlsx"

Set SearchRes = oExcel.Sheets("BTB-Workout").Range("C2:XDF1000").Find(date)

If NOT SearchRes is Nothing then
    SearchRes.Select
Else
    Msgbox("Not Found!")
End If
End Sub
VBEND

GetDate>today
VBRun>xlExample,today

[CZ]

Hi
Thank you for your reply. I tried it, but it doesn't work. It seems that if I take out the "oExcel.Range("C2:XDF1000").Select" line in the code the Workbook won't automatically go to that Worksheet that has the dates in it. Also I keep getting the msgbox "Not Found" when I know that today's date is in there. However the cells with the dates are based on formulas from other cells for example cell E5 has the date 07/07/14 in it and Cell T5 has the formula E5+7 which is 07/14/14. It is like that through out the entire worksheet. I apologize for not mentioning that before, but I'm guessing that may have something to do with the code not being able to find today's date.

Thanks again for trying.

[hagchr]

Hi again. I changed it slightly so that it sets focus to the right sheet and also that it searches for values (The constant xlValues=-4163 could vary depending on Excel version but hopefully ok for you). I am not sure if one also has to change to ensure the date formats are consistent (could the initial 0 you have for month/day 1-9 create a problem?). Maybe you can check if this works.

Code: Select all

VBSTART
Sub xlExample(date)
xlValues=-4163
Set oExcel = CreateObject("Excel.Application")
oExcel.Visible = true
oExcel.Workbooks.Open "C:\My Stuff\Data\microsoft excel 97\BTB.xlsx"
oExcel.Sheets("BTB-Workout").activate

Set SearchRes = oExcel.Sheets("BTB-Workout").Range("C2:XDF1000").Find(date,,xlValues)
If NOT SearchRes is Nothing then
    SearchRes.Select
Else
    Msgbox("Not Found!")
End If
End Sub
VBEND
GetDate>today
VBRun>xlExample,today

[CZ]

Thanks again for the reply, but unfortunately it didn't work. It does go to the correct worksheet, but it doesn't go to the date. I tried changing the dates so that they are like 9/1/14 instead of 09/01/14, but that didn't help. I did notice however that if I do a search in the spreadsheet itself (Ctrl+F) and enter for example 09/07/14 in the "Find what" box, change the "Look in: box to "Values" it finds the cells with no problem. So I figure that there has to be a VBScript to put all those things into play, but I can't figure it out. So I may figure out another way. It probably will be the long way around it, but at least it will get it done.

Thanks again for your help.

[CZ]

Hi hagchr,

Wanted to let you know that I finally figured out what you were trying to say about changing the date format. I was thinking that you were talking about changing the date format in the Excel program itself. I finally noticed that the date format on my computer was MM/dd/yyyy. When I changed it to MM/dd/yy your solution worked perfectly.

Thanks again for your help.