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]

check that you have a value in formatdate

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
    Dim sPathFile As String, wb As Workbook
  
    FileDate = Format(Range("B2").Value, "yyyy-mm-dd") 'or YOUR format
'     GetDate = Range("c2").Value
    formatdate = Format(Range("c2").Value, "dd.mmm.yy")

        'Workbooks.Open Filename:="c:\\zonaldemands.csv"
        'Windows("ZonalDemands.csv").Activate
        
    sPathFile = "[URL unfurl="true"]http://website.ca/ZonalDemands_"[/URL] & FileDate & ".csv"

    Set wb = Workbooks.Open(Filename:=sPathFile)
'    Windows("ZonalDemands_" & FileDate & ".csv").Activate
    With wb.Sheets("WhatSheetName")
        Set myrange = Cells.Find( _
            What:=formatdate, _
            after:=.Range("A2"), _
            LookIn:=xlValues, _
            lookat:=xlPart, _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, _
            MatchCase:=False)
        If Not myrange Is Nothing Then
            With myrange
                wb.Sheets("WhatSheetName").Range("A" & .Row, "G" & .Row + 167).Copy
            End With
            With Windows("Shadow Pricing 2007.xls")
                .Range("a5").PasteSpecial _
                    Paste:=xlPasteValues, _
                    Operation:=xlNone, _
                    SkipBlanks:=False, _
                    Transpose:=False
                .Range("g5:g200").Copy
                .Range("d5").PasteSpecial
                .Range("e5:g200").ClearContents
                .Range("a1").Select
            End With
        Else
        
        End If
    End With

    wb.Close SaveChanges:=False

    Exit Sub
Errorhandler:
    
    MsgBox (Err.Number & ", " & Err.Description)


End Sub

Skip,

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

 

[DianeA]

my error : Using the msgbox(getdate) immediately prior to the Cell.Find line I determined that the format was correct (10.Nov.07)

should have read "msgbox(formatdate)

 

[SkipVought]

Use this technique to find the actual values of variables and OBJECTS in your code.

faq707-4594

Skip,

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

 

[DianeA]

wow... as i mentioned i'm a newbie.. and it took me a while to get my head around the find.cell stmt. Pivottables is a whole new animal...

You don't think my method will work at all?

 

[SkipVought]

Pivottables? It JUST an example of OBJECTS on a sheet.

You have your own OBJECTS and variables. Look at them in the Watch Window. It's a learning process.

Skip,

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

 

[DianeA]

ok.. reading more thoroughly.. its not about pivottables... let me see if i can work through that example .. any help will be appreciated

 

[DianeA]

Ok... this is what I got..

I changed the format of formatdate to mm.dd.yyyy
I changed formatdate "as Date" instead "as string"
I created "retrievalcell" to equal target starting cell (A2)on remote spreadsheet


Watches showed:
formatdate as Date with value '11.10.2007'
retrievalcell as Variant/Date with value '01.01.2007' (althought the actual cell is formatted as dd.mmm.yy)

xlValues = -4163
xlPart = 2
xlByRows = 1
xlNext = 1

 

[SkipVought]

Is this all correct???

When the error happens, and you hit the DEBUG button, alt+F11 returns you to the following sheet...

Windows("ZonalDemands_" & FileDate & ".csv")

The line of code that errors is...

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

Skip,

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

 

[DianeA]

Yes to both

 

[DianeA]

This code works if i move the spreadsheet local to my desktop. I'm wondering if there are any references that i may be missing? just guessing

 

[SkipVought]

"...if i move the spreadsheet local to my desktop..."

What does that mean? WHICH spreadsheet? Where was it?

Skip,

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

 

[DianeA]

The spreadsheet is on a website. Its a spreadsheet that changes weekly and we retrieve data from it. The website is remote. The copy is a read-only copy. I saved a copy of it on the c: drive to test.. and the macro appears to work fine on that file.. (in the csv format).

 

[DianeA]

I did a watch on the same fields and ran the macro again but with the copy of the spreadsheet i saved locally.. And it appears that column A ("retrievalcell") is now showing up as a string with value of '01.Jan.07' in cell A2. I needed to change my macro so that "formatdate" is string with the format 'dd.mmm.yy' and it found my search date fine.

Diane

 

[SkipVought]

Ahhhhhh. Now you have another tool in your toolbox for discovering what's happening in your code.

Skip,

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

 

[DianeA]

It is a great tool... but it doesn't fix my problem... any ideas?

 

[SkipVought]

Instead of referencing the WINDOW, reference the Workbook.Worksheet objects...

    Set wb = Workbooks.Open(Filename:=sPathFile)
'    Windows("ZonalDemands_" & FileDate & ".csv").Activate
    With wb.Sheets("[b]WhatSheetName[/b]")
        Set myrange = [b][red].[/red][/b]Cells.Find( _
            What:=formatdate, _[b]
            after:=[red].[/red]Range("A2"), _[/b]
            LookIn:=xlValues, _
            lookat:=xlPart, _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, _
            MatchCase:=False)

'.........
    End With

Skip,

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

 

[DianeA]

This gives me 424, Object required error.

I removed FormatDate and use GetDate instead since it appears the formatting is the same anyhow. I only used FormatDate to convert from 10.01.2007 to 01.Oct.07

Also.. Do I also need to add the "." infront of Cells.Find and "Range("A2")?

What i have...
With Workbook.Worksheets("ZonalDemands_" & FileDate)
Set myrange = Cells.Find( _
What:=GetDate, _
After:=Range("a2"), _
LookIn:=xlValues, _
lookat:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not myrange Is Nothing Then
myrange.Activate
Else
MsgBox ("date not found")
End If
End With

 

[SkipVought]

"Do I also need to add the "." "

It was not an accident.

It means that that object refers to the WITH object, in the With...End With structure.

Skip,

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

 

[DianeA]

Thanks Skip...

I have found a way around this... I formatted the "A" column to my format I want and got it to worked.. I close the spreadsheet without saving anyhow...

Its most likely something basic i'm missing and it could be the custom formatting of "mm.ddd.yy" in the A column but I think enough time has been spent..

You have been a valueable resource and thanks again.!!

Diane