+ Reply to Thread
Results 1 to 7 of 7

Not understanding If Not..Then nothing

  1. #1
    davegb
    Guest

    Not understanding If Not..Then nothing

    I'm confused by using an "If not then nothing". It seems to me that if
    I do a find, and the text I searched for is found, then the value of
    the range it is found in would NOT be nothing. But it is. So is that
    correct, that when XL finds what it's looking for, the value of that
    range is "nothing"? I won't be surprised much if it's the opposite of
    what makes sense to me. Everything else in VBA is!
    Thanks for your help.


  2. #2
    davegb
    Guest

    Re: Not understanding If Not..Then nothing

    I did a more testing. It's always nothing! If it finds the text, the
    range variable is nothing. If it doesn't find the text, the range
    variable is nothing.
    That begs the question: If you do a "Find" in a macro, how do you know
    if a cell with that value was found? I copied a "If Not rangevariable
    is Nothing" from another thread which I thought would tell me if the
    value had been found. I guess it does something else. So, after you do
    a find, how do you know if it was found or not?
    Thanks!


  3. #3
    ben
    Guest

    Re: Not understanding If Not..Then nothing

    davegb,

    Your question seems a bit confusing. I use the find method quite
    frequently, and xl does not return an empty (Nothing) variable when the value
    is actually found, (Maybe xl is not actually finding it in your tests.)
    Ben

    --
    When you lose your mind, you free your life.


    "davegb" wrote:

    > I did a more testing. It's always nothing! If it finds the text, the
    > range variable is nothing. If it doesn't find the text, the range
    > variable is nothing.
    > That begs the question: If you do a "Find" in a macro, how do you know
    > if a cell with that value was found? I copied a "If Not rangevariable
    > is Nothing" from another thread which I thought would tell me if the
    > value had been found. I guess it does something else. So, after you do
    > a find, how do you know if it was found or not?
    > Thanks!
    >
    >


  4. #4
    davegb
    Guest

    Re: Not understanding If Not..Then nothing

    Ben, Thanks for your reply.
    Yes, XL is finding it. It's just still saying that FoundCell is
    nothing.
    Dave

    ben (remove this if mailing direct) wrote:
    > davegb,
    >
    > Your question seems a bit confusing. I use the find method quite
    > frequently, and xl does not return an empty (Nothing) variable when the value
    > is actually found, (Maybe xl is not actually finding it in your tests.)
    > Ben
    >
    > --
    > When you lose your mind, you free your life.
    >
    >
    > "davegb" wrote:
    >
    > > I did a more testing. It's always nothing! If it finds the text, the
    > > range variable is nothing. If it doesn't find the text, the range
    > > variable is nothing.
    > > That begs the question: If you do a "Find" in a macro, how do you know
    > > if a cell with that value was found? I copied a "If Not rangevariable
    > > is Nothing" from another thread which I thought would tell me if the
    > > value had been found. I guess it does something else. So, after you do
    > > a find, how do you know if it was found or not?
    > > Thanks!
    > >
    > >



  5. #5
    davegb
    Guest

    Re: Not understanding If Not..Then nothing

    Here's my code:

    'Test for Client Disab
    On Error Resume Next
    RecSht.Range("1:1").Select
    FoundCell = Cells.find(What:="Client Disab", After:=ActiveCell,
    LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
    _
    MatchCase:=False)

    If Not FoundCell Is Nothing Then
    RecSht.Range("aa4").Copy Destination:=CtyExtr.Range("N5")

    I've done a watch on "FoundCell", dimmed as range,on datasheets with
    and without the searched for text ("Client Disab"). Whether the text is
    found or not, FoundCell is nothing.
    Any suggestions?


  6. #6
    Chip Pearson
    Guest

    Re: Not understanding If Not..Then nothing

    You're missing a Set command.

    FoundCell = Cells.find(What:="Client Disab",
    After:=ActiveCell,
    should be
    Set FoundCell = Cells.find(What:="Client Disab",
    After:=ActiveCell,



    --
    Cordially,
    Chip Pearson
    Microsoft MVP - Excel
    Pearson Software Consulting, LLC
    www.cpearson.com


    "davegb" <[email protected]> wrote in message
    news:[email protected]...
    > Here's my code:
    >
    > 'Test for Client Disab
    > On Error Resume Next
    > RecSht.Range("1:1").Select
    > FoundCell = Cells.find(What:="Client Disab",
    > After:=ActiveCell,
    > LookIn:=xlFormulas, _
    > LookAt:=xlPart, SearchOrder:=xlByRows,
    > SearchDirection:=xlNext,
    > _
    > MatchCase:=False)
    >
    > If Not FoundCell Is Nothing Then
    > RecSht.Range("aa4").Copy
    > Destination:=CtyExtr.Range("N5")
    >
    > I've done a watch on "FoundCell", dimmed as range,on datasheets
    > with
    > and without the searched for text ("Client Disab"). Whether the
    > text is
    > found or not, FoundCell is nothing.
    > Any suggestions?
    >




  7. #7
    davegb
    Guest

    Re: Not understanding If Not..Then nothing

    That did it! Thanks, Chip!

    Chip Pearson wrote:
    > You're missing a Set command.
    >
    > FoundCell = Cells.find(What:="Client Disab",
    > After:=ActiveCell,
    > should be
    > Set FoundCell = Cells.find(What:="Client Disab",
    > After:=ActiveCell,
    >
    >
    >
    > --
    > Cordially,
    > Chip Pearson
    > Microsoft MVP - Excel
    > Pearson Software Consulting, LLC
    > www.cpearson.com
    >
    >
    > "davegb" <[email protected]> wrote in message
    > news:[email protected]...
    > > Here's my code:
    > >
    > > 'Test for Client Disab
    > > On Error Resume Next
    > > RecSht.Range("1:1").Select
    > > FoundCell = Cells.find(What:="Client Disab",
    > > After:=ActiveCell,
    > > LookIn:=xlFormulas, _
    > > LookAt:=xlPart, SearchOrder:=xlByRows,
    > > SearchDirection:=xlNext,
    > > _
    > > MatchCase:=False)
    > >
    > > If Not FoundCell Is Nothing Then
    > > RecSht.Range("aa4").Copy
    > > Destination:=CtyExtr.Range("N5")
    > >
    > > I've done a watch on "FoundCell", dimmed as range,on datasheets
    > > with
    > > and without the searched for text ("Client Disab"). Whether the
    > > text is
    > > found or not, FoundCell is nothing.
    > > Any suggestions?
    > >



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1