1

I am using a macro to find a mismatch. When a mismatch is found an "x" is placed in a cell. My macro looks for the first cell with an "x" and then opens a msgbox, saying there is a mismatch. If no mismatch is found i want it to exit. However, if there is no mismatch I get: Runtime error 91: Object variable not set (Error 91)

This is the code that errors:

Range(Selection, Selection.End(xlDown)).Select

If Selection.Find(What:="x", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
    , SearchFormat:=False).Activate = True Then


 MsgBox ("There is an issue with Validation for this file. Please open the xx_xxxx.csv file and verify that you are entering the correct data.")
Else: Exit Sub

End If

Thank you for any suggestions

Community
  • 1
  • 1
user2796515
  • 264
  • 3
  • 16
  • Two links for you `1` [INTERESTING READ](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select) `2` [Using FIND](http://www.siddharthrout.com/2011/07/14/find-and-findnext-in-excel-vba/) – Siddharth Rout Nov 24 '13 at 08:14
  • Thank you, I have done a once over of the links and find them very useful. I will keep them in my bag of tools. – user2796515 Nov 29 '13 at 07:19

1 Answers1

2

You should use this:

Sub sof20172114Findx()
  Dim objRange

  Range(Selection, Selection.End(xlDown)).Select

  Set objRange = Selection.Find(what:="x", After:=ActiveCell, LookIn:=xlValues _
    , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext _
    , MatchCase:=False, SearchFormat:=False)

  If (objRange Is Nothing) Then
    'MsgBox "No x found."
    Exit Sub
  Else
    MsgBox "There is an issue with Validation for this file. Please open the xx_xxxx.csv file and verify that you are entering the correct data."
    objRange.Activate
  End If
  Set objRange = Nothing
End Sub

If there is no x stirng found, Selection.Find() returns Nothing, as Nothing has no .Activate method, so error occurs in your original code.

jacouh
  • 8,473
  • 5
  • 32
  • 43
  • Flawless.. Thank you very much Jacouh! And thank you for the explanation. I wasn't sure how to work around the code returning "nothing". Thanks! – user2796515 Nov 29 '13 at 06:53