Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Object Error (Excel 2000)

    Hi all,

    I'm getting an ObjectVariable not set error, what I don't understand is on my office computer(Windows NT), I am not getting this error, but on my home computer with Windows XP I am getting this error. The problem is with Cells.find, can anyone see anything wrong with my code - is there something different about XP and NT as far as Excel is concerned?

    Thanks,
    Darryl.


    Private Sub Calendar1_Click()
    Dim x As String 'calender date value
    Dim WeekStaff As Integer 'staff week
    Dim WeekSup As Integer 'Lob and Sup staff
    Application.ScreenUpdating = False
    Sheets("myweeks").Select
    x = UserForm1.Calendar1.Value
    UserForm1.Height = 520.5
    UserForm1.Width = 499.25
    Cells.Find(what:=x, After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ <--------Error is here.
    MatchCase:=True).Activate
    ActiveCell.Offset(0, 2).Select
    WeekStaff = ActiveCell.Value
    ActiveCell.Offset(0, 1).Select
    WeekSup = ActiveCell.Value
    UserForm1.MSPLb = WeekSup
    UserForm1.PSLLB = WeekStaff
    Select Case WeekStaff
    Case 1

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Object Error (Excel 2000)

    The result of Find is a Range object. If the search value is not found, this object is set to Nothing, and trying to activate it results in the error you get. You should check for this:

    Dim rngFound As Range
    Set rngFound = Cells.Find(what:=x, After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
    MatchCase:=True)
    ' Did we find anything?
    If rngFound Is Nothing Then
    ' Not found, warn the user and get out
    MsgBox "Not found!"
    Exit Sub
    End If
    WeekStaff = rngFound.Offset(0, 2).Value
    WeekSup = rngFound.Offset(0, 3).Value
    ...

    Note that it wasn't necessary to select the cell where the value was found.

  3. #3
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Object Error (Excel 2000)

    Thanks Hans,

    It worked great, also, I figured out that my variable for the date was wrong, I had it as a string.

    Darryl.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •