91 object variable or with block variable not set

D

DianeA

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
 
D

DianeA

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)
 
J

Jim Thomlinson

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

Jim Thomlinson


DianeA said:
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 said:
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
Click to expand...
Click to expand...
 
D

DianeA

10-Nov-07

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

Jim Thomlinson


DianeA said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
A

Andy Pope

Hi,

If using the hard coded value of "10-Nov-07" works try adjusting your
format replacing the periods with dashes.

formatdate = Format([GetDate], "dd-mmm-yy")

Cheers
Andy
 
D

DianeA

my answer may have been vague...

When i pass the value through the "formatdate" variable and do a check using
msgbox(formtdate) immediately prior to the find.. it has "10-Nov-07"

The A column in the spreadsheet cell that i'm looking in is formatted to
custom dd-mmm-yy


DianeA said:
10-Nov-07

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

Jim Thomlinson


DianeA said:
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)



:

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
Click to expand...
Click to expand...
Click to expand...
 
D

DianeA

Sorry... my error... the date 10.Nov.07 is returned and the spreadsheet shows
10.Nov.07 in column A starting at row 2

Andy Pope said:
Hi,

If using the hard coded value of "10-Nov-07" works try adjusting your
format replacing the periods with dashes.

formatdate = Format([GetDate], "dd-mmm-yy")

Cheers
Andy
--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
DianeA said:
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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Object variable or with Block variable not set 9
Runtime error 91: Object variable or With block variable not set 8
If Cells.Find returns run-time error '91': Object variableor With block variable not set 1
Problem with hyperlink macro 0
Object variable or With block variable not set 2
Using VLookup with 2 Workbooks 0
Error Number: 91 object variable or With block Variable not set 1
Set destination sheet based on variable and paste data assistance 13

Top