Object Variable or With Block Variable no set - EXCEL 1

[Adker]

Private Sub CommandButton1_Click()
On Error GoTo errorHandler
Dim startDate As String
Dim stopDate As String
Dim startColumn As Integer
Dim stopColumn As Integer
Dim StartCol As String
Dim StopCol As String
MsgBox Application.Version

Worksheets("Charts & Graphs&quot.Activate


startDate = Range("C33&quot
If startDate = "" Then End
stopDate = Range("C34&quot
If stopDate = "" Then End
Worksheets("ChartsData&quot.Activate
ActiveSheet.Range("F1&quot.Select
ActiveSheet.Range(Selection, Selection.End(xlToRight)).Select

startColumn = Selection.Find(What:=startDate, _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext).Column

StartCol = Cells(1, startColumn).Address(True, False, xlA1)
StartCol = Left(StartCol, InStr(1, StartCol, "$&quot - 1)

stopColumn = Selection.Find(stopDate, _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext).Column
StopCol = Cells(1, stopColumn).Address(True, False, xlA1)
StopCol = Left(StopCol, InStr(1, StopCol, "$&quot - 1)
Worksheets("ChartsData&quot.Columns(StartCol & ":" & StopCol).Select
ActiveSheet.Range("E9:E20," & StartCol & "9:" & StopCol & "20&quot.Select
Selection.Copy
Sheets("Charts & Graphs&quot.Select
ActiveSheet.ChartObjects("Chart 1&quot.Activate
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection.Paste Rowcol:=xlColumns, SeriesLabels:=False, _
CategoryLabels:=True, Replace:=False, NewSeries:=True
End
errorHandler:
MsgBox "There has been an error: " & Error() & Chr(13) _
& "Ending Sub.......Please try again", 72
End Sub

Can someone explain to me why I get a Variable Not Set error when I run this code on WinNT using Excel 97. Works fine on Win2000 with Excel 2000 AND Win98 with Excel 97.

Thanks in advance!

John

 

[dsi]

Which line does it error on? Remove the ErrHndlr and test in Debug mode.

 

[Adker]

startColumn = Selection.Find(What:=startDate, _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext).Column


Sorry, I should have included this in the initial post.

 

[xlbo]

FIND needs an object and therefore the SET statement
set fCell = Selection.Find(What:=startDate, _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext)
startcolumn = fCell.column

You will also need to do this for stopColumn

HTH Rgds
~Geoff~

 

[Adker]

Perfect! Thank you for the help.

John

 

[Adker]

I spoke too soon. This still does not work in Excel 97 running on WinNT. Other platforms are fine.

Private Sub CommandButton1_Click()
On Error GoTo errorHandler
Dim startDate As String
Dim stopDate As String
Dim startColumn As Integer
Dim stopColumn As Integer
Dim StartCol As String
Dim StopCol As String

Worksheets("Charts & Graphs&quot.Activate
startDate = Range("C33&quot
If startDate = "" Then End
stopDate = Range("C34&quot
If stopDate = "" Then End
Worksheets("ChartsData&quot.Activate
ActiveSheet.Range("F1&quot.Select
ActiveSheet.Range(Selection, Selection.End(xlToRight)).Select
Set fCell = Selection.Find(What:=startDate, _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext)
startColumn = fCell.Column
StartCol = Cells(1, startColumn).Address(True, False, xlA1)
StartCol = Left(StartCol, InStr(1, StartCol, "$&quot - 1)

Set gCell = Selection.Find(stopDate, _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext)
stopColumn = gCell.Column
StopCol = Cells(1, stopColumn).Address(True, False, xlA1)
StopCol = Left(StopCol, InStr(1, StopCol, "$&quot - 1)
Worksheets("ChartsData&quot.Columns(StartCol & ":" & StopCol).Select
ActiveSheet.Range("E9:E20," & StartCol & "9:" & StopCol & "20&quot.Select
Selection.Copy
Sheets("Charts & Graphs&quot.Select
ActiveSheet.ChartObjects("Chart 1&quot.Activate
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection.Paste Rowcol:=xlColumns, SeriesLabels:=False, _
CategoryLabels:=True, Replace:=False, NewSeries:=True
End
errorHandler:
MsgBox "There has been an error: " & Error() & Chr(13) _
& "Ending Sub.......Please try again", 72
End Sub


Fails at the selection.find lines.

 

[xlbo]

Do you get the same error messgae ?? Rgds
~Geoff~

 

[Adker]

Same error.

Object Variable or With Block Variable no set.

 

[xlbo]

Don't know if it'll make any difference but I always use WITH for my finds
With selection
Set fCell = .Find(What:=startDate, _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext)
startColumn = fCell.Column
end with

Here is some more info re: Err91 (Obj var with block var not set)
There are two steps to creating an object variable. First you must declare the object variable. Then you must assign a valid reference to the object variable using the Set statement. Similarly, a With...End With block must be initialized by executing the With statement entry point. This error has the following causes and solutions:

· You attempted to use an object variable that isn't yet referencing a valid object.

Specify or respecify a reference for the object variable. For example, if the Set statement is omitted in the following code, an error would be generated on the reference to MyObject:

Dim MyObject As Object ' Create object variable.
Set MyObject = Sheets(1) ' Create valid object reference.
MyCount = MyObject.Count ' Assign Count value to MyCount.

· You attempted to use an object variable that has been set to Nothing.

Set MyObject = Nothing ' Release the object.
MyCount = MyObject.Count ' Make a reference to a released object.

Respecify a reference for the object variable. For example, use a new Set statement to set a new reference to the object.

· The object is a valid object, but it wasn't set because the object library in which it is described hasn't been selected in the References dialog box.

Select the object library in the Add References dialog box.

· The target of a GoTo statement is inside a With block.

Don't jump into a With block. Make sure the block is initialized by executing the With statement entry point.

· You specified a line inside a With block when you chose the Set Next Statement command.

The With block must be initialized by executing the With statement.

For additional information, select the item in question and press F1.

HTH Rgds
~Geoff~

 

[Adker]

Still no go.

Near as I can tell all the requirements listed above are satisfied.

Frustrating because it runs on all platforms except the one used by most of the users on this intranet(NT & Ecxcel97).

Perhaps there is a better way of doing what I am attempting. I have a dynamic range of data(new data is added automatically every day). I need to be able to chart the data based on input from the user where they select a start and stop date.

Thanks.

 

[xlbo]

Weird - I'm on NT and 97 but don't get that problem - feel free to mail me the workbook
Geoff.Barraclough@Punchpubs.co.uk Rgds
~Geoff~

 

[dwilson01]

Here is some info from a thread posted Sept. 9, 2002. The thread is thread707-357558 and the discussion is about not being able to use .Find to search and locate Dates.

Brian had this to say

I wonder if you are formating your date correctly. This works OK for me (XL97):=
'-------------------------------------------

Sub test()
Dim FoundCell
Dim MyDate As Date
MyDate = #1/1/01#
'-------------------
Set FoundCell = ActiveSheet.Cells.Find(what:=MyDate)
    If FoundCell Is Nothing Then
        MsgBox ("Not Found")
    Else
        MsgBox (FoundCell.Address)
    End If
End Sub

'--------------------------------------------

Regards
BrianB
** Let us know if you get something that works !

There are a couple of other posts as well. It seems that there are differences in versions of Excel and I don't have 97 so I feel I can be of little more help.

One other thing, this error occurs when you don't test for the object set, (i.e. if the .Find doesn't find anything) and you try to set a variable to some property of the undefined object.

Dave

 

[Adker]

Here is the latest sub with changes made to reflect suggestions here. That last thing I did was to add the format lines for the start and stop dates. Again it works fine with the exception of Excel97 on WinNT. Other platforms tested are Excel2000 on Win2000 and Excel97 on Win98.

Private Sub CommandButton1_Click()
On Error GoTo errorHandler
Dim startDate As String
Dim stopDate As String
Dim startColumn As Integer
Dim stopColumn As Integer
Dim StartCol As String
Dim StopCol As String
Dim fCell As Object
Dim gCell As Object


Worksheets("Charts & Graphs&quot.Activate
startDate = Range("C33&quot
If startDate = "" Then End
startDate = Format(startDate, "m/dd/yyyy&quot
stopDate = Range("C34&quot
If stopDate = "" Then End
stopDate = Format(stopDate, "m/dd/yyyy&quot
Worksheets("ChartsData&quot.Activate
ActiveSheet.Range("F1&quot.Select
ActiveSheet.Range(Selection, Selection.End(xlToRight)).Select
With Selection
Set fCell = Selection.Find(What:=startDate, _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext)
startColumn = fCell.Column
End With
StartCol = Cells(1, startColumn).Address(True, False, xlA1)
StartCol = Left(StartCol, InStr(1, StartCol, "$&quot - 1)
With Selection
Set gCell = Selection.Find(stopDate, _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext)
stopColumn = gCell.Column
End With
StopCol = Cells(1, stopColumn).Address(True, False, xlA1)
StopCol = Left(StopCol, InStr(1, StopCol, "$&quot - 1)
Worksheets("ChartsData&quot.Columns(StartCol & ":" & StopCol).Select
ActiveSheet.Range("E9:E20," & StartCol & "9:" & StopCol & "20&quot.Select
Selection.Copy
Sheets("Charts & Graphs&quot.Select
ActiveSheet.ChartObjects("Chart 1&quot.Activate
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection.Paste Rowcol:=xlColumns, SeriesLabels:=False, _
CategoryLabels:=True, Replace:=False, NewSeries:=True
End
errorHandler:
MsgBox "There has been an error: " & Error() & Chr(13) _
& "Ending Sub.......Please try again", 72
End Sub

 

[Adker]

Finally something that works on all platforms attempted!

Set fCell = Selection.Find(What:=DateValue(startDate), _
LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext)
startColumn = fCell.Column

I found this solution at

http://www.cpearson.com/excel/DateTimeVBA.htm

Thank for everyones help! I hope this proves useful to those that follow...

John

 

[xlbo]

Adker - I have returned your file with a slightly different working method - tested on NT/97 Rgds
~Geoff~

 

[Adker]

Geoff,

Thanks very much for your help. Your solution works as well. I am including a section of the code to share with others your fix.

Worksheets("Charts & Graphs&quot.Activate
startDate = Range("C33&quot
If startDate = "" Then End
startDate = Format(startDate, Sheets("ChartsData&quot.Range("F1&quot.NumberFormat)
stopDate = Range("C34&quot
If stopDate = "" Then End
stopDate = Format(stopDate, Sheets("ChartsData&quot.Range("F1&quot.NumberFormat)
Worksheets("ChartsData&quot.Activate
ActiveSheet.Range("F1&quot.Select
ActiveSheet.Range(Selection, Selection.End(xlToRight)).Select
With Selection
Set fCell = .Find(What:=startDate, _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext)
If fCell Is Nothing Then
MsgBox "Invalid Start Date - Please try again"