Search code examples
dateobjectvbscriptsyntax-errorrequired

Error with the Excel.Find function in VBScript

I am trying to create a VB script that searches through a column of dates and returns the address of the cell with todays date. For some reason I keep getting an "Object required: 'FoundCell'" error. Could someone have a look at my code and correct me? I can read out the date using WScript.Echo, but once I use it in the find function it immediately gives me the error.

Set oExcel = CreateObject("Excel.Application")
Set wshShell = CreateObject("Wscript.Shell")

File_Path = "D:\Work\Personal Timemanagement\test.xlsx"


Set oData = oExcel.Workbooks.Open(File_Path)

WHAT_TO_FIND = Date()
WScript.Echo WHAT_TO_FIND

Set FoundCell = oData.Worksheets("tst2").Range("A1:A40").Find(WHAT_TO_FIND)
oExcel.Cells(4,4) = FoundCell.Address


oExcel.ActiveWorkbook.SaveAs "D:\Work\Personal Timemanagement\test2.xlsx"
oExcel.ActiveWorkbook.Close

oExcel.Application.Quit

WScript.Quit

Thanks for the help!

Solution

  • WHAT_TO_FIND1 returns value like #14/10/2014#.So replace the # with nothing using WHAT_TO_FIND1=Replace(WHAT_TO_FIND,"#","Nothing").

    Once replaced the above code will work