| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Need help with Error 'object variable or with block variable not set' | Excel Programming | |||
| Run-time Error'91: Object variable or With block variable not set | Excel Programming | |||
| Run-time error '91': "Object variable or With block variable not set | Excel Programming | |||
| Cells.Find error Object variable or With block variable not set | Excel Programming | |||
| Pivot Table - Object variable or with block variable not set? | Excel Programming | |||
Linear Mode
