Error 91, with Cell.Find

[DianeA]

I am new to VB so bear with me... I've got a subroutine which opens a CSV spreadsheet on a website. I need to grab some data based on a Date. I type in the requested date on my spreadsheet and have the macro format it to the same format on the remote spreadsheet (dd.mmm.yy) and use the Cell.Find in order to find the row in which to start my copy of data from. Problem is i get error 91, Object variable or with Block Variable not set. I inserted code to properly handle the msg but it doesn't explain why it doesn't find the particular date in the spreadsheet which it does exist. Using the msgbox(getdate) immediately prior to the Cell.Find line I determined that the format was correct (10.Nov.07) which is in column A. I copied this remote spreadsheet to my harddrive and ran the code with no problems. I'm stumped.

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

 

[SkipVought]

" custom formatting of "mm.ddd.yy" "

1. I think you meant, dd.mmm.yy

2. There is a danger in using this format. Yes, you can custom format a DOT delimiter in a date, but Excel does not recognize dd.mmm.yy as a STRING that you intend to convert to a DATE, where if you were to enter dd-mmm-yy or dd/mmm/yy in ANY cell, Excel would CONVERT the string to a date.

faq68-5827



Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[DianeA]

Sorry for the delay... i've been away for a couple of days...

anyhow .. hopefully i can explain this a bit better..

In the target file.. in column A... as an example... i click on A2.. the formula bar shows me 01.01.2007 but the actual cell shows 01.jan.07 (custom format of dd.mmm.yy)

Initially my variable in which i used to find the appropriate cell in the target file looked like this:
Dim formatdate As String
formatdate = Format([GetDate], "dd.mmm.yy")

But this didn't get me anywhere and your "watched" idea showed 01.01.2007. So I just used GetDate which had a value of 10.11.2007:
Dim GetDate As Date

Now the target file in fact had this date in the "A" column so I don't know why it wouldn't find it. So to finally resolve my issue... I formated "A" column to mm.dd.yyyy as is shown in the formula bar. Don't know why but it works.