Results 1 to 3 of 3
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Peculiar variable behaviour (WinXP, XL2003)

    I'm having a problem with a variable that is behaving in a most peculiar way! The code reads as follows (oCell is defined as a range):

    Set oCell = Nothing
    Set oCell = i.Range("A51:A62").Find(What:="Head Count Previous Year", LookAt:=xlWhole, MatchCase:=False)
    If Not oCell = Empty Then ...

    This falls over at the third line saying the variable is not set and a watch put on it tells me its value is nothing; if I change that line to read:

    If Not oCell = Empty And Not oCell = Nothing Then ...

    I'm told it's an "invalid use of object" - and the watch now says oCell's value is Empty!!

    Obviously I'm doing something wrong here, but I'm danged if I can see what it could be that would make those two results possible in the same line of code!
    <img src=/S/confused3.gif border=0 alt=confused3 width=45 height=45>
    Beryl M


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

    Re: Peculiar variable behaviour (WinXP, XL2003)

    Nothing is not a real value, so you can't use

    If Not oCell = Nothing Then ...

    Instead, try

    If Not oCell Is Nothing Then ...

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Peculiar variable behaviour (WinXP, XL2003)

    Sigh ... <img src=/S/sigh.gif border=0 alt=sigh width=15 height=15> That was it.

    I knew it had to be something silly and simple, but ...! <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

    Thanks, Hans - good thing someone around here knows how to use VBA!

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>
    Beryl M


Posting Permissions

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