VBA Error Handling when dealing with Cells.Find

[Nyanko]

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

    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

 

[Richard Schollar]

Hi

Something like:

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

 

[Nyanko]

That works perfectly many thanks.

 

[DanielCrinson]

Immensely useful cheers.

 

[mexindian]

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

 

[awsmitty]

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

 

[awsmitty]

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

 

[willstandbyyou]

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

 

[pgc01]

Hi

Did you check the parameters of the Find?