RE: Error 91, Object var or With block not set

From: Walter (Walter_at_discussions.microsoft.com)
Date: 08/23/04 

Date: Mon, 23 Aug 2004 16:37:03 -0700

That solved it! Many, many thanks!

Walter

"sebastienm" wrote:

> Hi Walter,
>
> It is probably because
> MyRange.Find(what:=myDate, lookat:=xlPart)
> returns Nothing, therefore applying the Select:
> MyRange.Find(what:=myDate, lookat:=xlPart).Select
> on Nothing generates an error.
> (assuming myRange is not Nothing)
>
> To make sure of that, use instead:
> Dim rg as range
> '... prior code
> set rg = myRange.Find(what:=myDate, lookat:=xlPart)
> if rg is nothing then
> Msgbox "Value not found. Search for: " & mydate & " in " myRange.Address
> Exit Sub
> End if
> Rg.Select
>
> If it is the problem, the above code will show what you are looking for
> (mydate) and where (address of myrange)
> Probably mydate is not in a 'searchable' format. Try to replace mydate in
> the Find method by:
> - Format(mydate,"mm/dd/yyyy") ' formatted string as the expected date
> format in myRange
> or
> - datevalue(mydate)
>
> Regards,
> Sébastien
> "Walter" wrote:
>
> > What am I doing wrong here?
> >
> > I've searched this discussion group, and google groups, but can't find the
> > solution to the error, or an alternate way to code this. This code sorts the
> > work*** by the dates in column B, then searches the cells in the column
> > looking for the last instance of myDate. (I need the address so I can select
> > a range and move it to another work***.) But I keep getting stuck with this
> > "Object var... not set" error.
> >
> > If I'm missing something fundamental here, please point me in the right
> > direction. I've spent several hours looking for and trying alternate ways of
> > doing this, and researching setting object variables, but I keep ending up
> > back at the same error.
> >
> > Private Sub btnDate_Click()
> > UserForm1.Hide
> > Range("A1:E261").Sort Key1:=Range("B1"), Order1:=xlAscending, Header:= _
> > xlGuess, OrderCustom:=1, MatchCase:=False,
> > Orientation:=xlTopToBottom, _
> > DataOption1:=xlSortNormal
> > If opt8.Value = True Then iDays = 9
> > If opt15.Value = True Then iDays = 16
> > If opt31.Value = True Then iDays = 32
> > 'using "Set myDate = Date - iDays" gets error on next line
> > myDate = Date - iDays
> > 'select column B to search in
> > Cells(1, 2).Activate
> > ActiveCell.End(xlDown).Select
> > strLast = Selection.Address
> > Set myRange = Range("B1", strLast)
> > 'as written, error occurs on next line
> > myRange.Find(what:=myDate, lookat:=xlPart).Select
> > strLast = Selection.Address
> > Set myRange = Nothing
> > End Sub
> >
> > Any help would be extremely appreciated!