91 object variable or with block variable not set
 

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

91 object variable or with block variable not set
 

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

91 object variable or with block variable not set
 

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

91 object variable or with block variable not set
 

10-Nov-07

"Jim Thomlinson" wrote:

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

91 object variable or with block variable not set
 

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" wrote in message
...
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

91 object variable or with block variable not set
 

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" wrote:

10-Nov-07

"Jim Thomlinson" wrote:

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

91 object variable or with block variable not set
 

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" wrote:

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" wrote in message
...
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