If you step through the code what is the value of formatdate when it goes to
do the find?
--
HTH...

Jim Thomlinson


"DianeA" wrote:

When I change "formatdate" in the statement below to "10-Nov-07" I don't get
the error message and all is fine

Set myrange = Cells.Find(What:=formatdate, LookIn:=xlValues,
lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False)



"DianeA" wrote:

I'm a newbie to VB.. I'm trying to open a spreadsheet (CSV) on a remote
website, Find a specfic date in column A and determine row in order to get
the specific data I need from the sheet. Spreadsheet opens and I get the
"91" error which I think because it can't find that date. But when I copy
the spreadsheet to my local harddrive and run the code again, it works.
I have verified that the correct formatted date is in the GetDate field by
using msgbox prior to the FIND stmt.

Sub GET_ONTDEMAND()
On Error GoTo Errorhandler

Dim GetDate As Date
Dim rowVal As Integer
Dim FileDate As String
Dim formatdate As String
Dim myrange As Range

FileDate = Range("B2").Value
GetDate = Range("c2").Value
formatdate = Format([GetDate], "dd.mmm.yy")



'Workbooks.Open Filename:="c:\\zonaldemands.csv"
'Windows("ZonalDemands.csv").Activate

Workbooks.Open Filename:="http://website.ca/ZonalDemands_" &
FileDate & ".csv"
Windows("ZonalDemands_" & FileDate & ".csv").Activate
Range("a2").Select

Set myrange = Cells.Find(What:=formatdate, LookIn:=xlValues,
lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False)
If Not myrange Is Nothing Then myrange.Activate
rowVal = ActiveCell.Row
Range("A" & rowVal, "G" & rowVal + 167).Select
Selection.Copy
Windows("Shadow Pricing 2007.xls").Activate
Range("a5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("g5:g200").Copy
Range("d5").PasteSpecial
Range("e5:g200").ClearContents
Range("a1").Select
Workbooks("http://website.ca/ZonalDemands_" & FileDate &
".csv").Close SaveChanges:=False


Exit Sub
Errorhandler:

MsgBox (Err.Number & ", " & Err.Description)


End Sub