Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: VBA Error Handling when dealing with Cells.Find

  1. #1
    Board Regular
    Join Date
    Sep 2005
    Location
    Huntingdon, Cambridgeshire. UK
    Posts
    437
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Error Handling when dealing with Cells.Find

    Hi,

    I'm trying to use a find method to look up a variable in a 2000+ list of data. I'm currently using this code :

    I need to see if the variable strAdLink1 exists on sheet AdLinks and then to return a Yes/No in the relevant cell on sheet XML Data

    Code:
        For a = 3 To lngXMLLastRow
            strAdLink1 = Cells(a, 1).Value
            For b = 1 To lngLnkLastRow
                Sheets("AdLinks").Select
                Range("A1").Select
                x = Cells.Find(What:=strAdLink1, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
                If IsError(x) Then
                    strAdLinkFlag = "No"
                    Sheets("XMLData").Select
                    Cells(a, 3) = strAdLinkFlag
                    Exit For
                Else
                    strAdLinkFlag = "Yes"
                    Sheets("XMLData").Select
                    Cells(a, 3) = strAdLinkFlag
                    Exit For
                End If
            Next b
        Next a

    The code if great at finding the date but as soon as it hits a value that isn't in the AdLinks worksheet then I get the error : Run Time Error 91 : Object Variable or With Blockvariable not set


    Any adise would be greatfully recieved
    Home running XL2013 on Win10
    Work running 365 on Win7

    MOS Excel 2010 Expert - Sep 2015
    MOS Excel 2013 Expert - Feb 2016
    MOS Office 2013 Master - Nov 2016

    www.about.me/AboutCatherine
    https://uk.linkedin.com/in/catparkinson

    Excel is more than a career... it's a life choice !

  2. #2
    MrExcel MVP
    Moderator
    Inactive
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA Error Handling when dealing with Cells.Find

    Hi

    Something like:

    Code:
    Dim rngFound As Range
     
    Set rngFound = Sheets("WhateverSheet").UsedRange.Find(What:="SoughtValue",LookIn:=xlFormulas)
     
    If Not rngFound Is Nothing Then
      'you found the value - do whatever
    Else
      ' you didn't find the value
    End if
    Richard Schollar

    Using xl2013

  3. #3
    Board Regular
    Join Date
    Sep 2005
    Location
    Huntingdon, Cambridgeshire. UK
    Posts
    437
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Error Handling when dealing with Cells.Find

    That works perfectly many thanks.
    Home running XL2013 on Win10
    Work running 365 on Win7

    MOS Excel 2010 Expert - Sep 2015
    MOS Excel 2013 Expert - Feb 2016
    MOS Office 2013 Master - Nov 2016

    www.about.me/AboutCatherine
    https://uk.linkedin.com/in/catparkinson

    Excel is more than a career... it's a life choice !

  4. #4
    New Member
    Join Date
    Jan 2014
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Error Handling when dealing with Cells.Find

    Immensely useful cheers.

  5. #5
    New Member
    Join Date
    Sep 2014
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Error Handling when dealing with Cells.Find

    Perfect. I knew there had to be a way to do it w/out error handling. Thanks!

  6. #6
    New Member
    Join Date
    Oct 2012
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Error Handling when dealing with Cells.Find

    How would I use this except substituting UsedRange with a user defined range such as Range(Cells(10,5), Cells(200, 5))

  7. #7
    New Member
    Join Date
    Oct 2012
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Error Handling when dealing with Cells.Find

    I found a solution,

    Set ws = Sheets("ChooseSheet")
    With ws
    Set rngFound = Range(Cells(10, 5), Cells(200, 5)).Find(What:="Non Performers", LookIn:=xlFormulas)
    End With
    If Not rngFound Is Nothing Then
    'you found the value - do whatever
    rngFound.Activate
    rnum = ActiveCell.Row
    cnum = ActiveCell.Column
    Else
    ' you didn't find the value
    End If

    If anyone knows (sees) a better way, please post. Humm, now if I use

    Set rngFound = Sheets("ChooseSheet").Range(Cells(10, 5), Cells(200, 5)).Find(What:="Non Performers", LookIn:=xlFormulas)

    it seems to work. I must have had a typo but didn't see it, sorry

  8. #8
    Board Member
    Join Date
    Dec 2015
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Error Handling when dealing with Cells.Find

    Hi All,

    I have a similar requirement but when I used this code, to find the word "ERROR", it is also searching in the formula and taking action. I needed excel to check only in the outcome / result of the formula but not in the formula (contents of the formula). Could anyone please help?

    Thank you in Advance,
    Syed Anwar

  9. #9
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    19,188
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA Error Handling when dealing with Cells.Find

    Hi

    Did you check the parameters of the Find?
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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