If Not function proceeds when value *is* the one specified

by

Use On Error Resume Next judiciously.

Don’t use On Error Resume Next for the entire code – it will hide all your errors. Use it only when it’s really needed.

Using On Error Resume Next means telling the code to Shut UP and do what you want. In most cases it will do what you want… Shut Up and perform… but then you will not get the expected results or totally wrong results as shown below !!! (SiddharthRout ©:)

enter image description here

Change

Err.Clear
On Error Resume Next
Set wsDel = Sheets("Matching Contacts")
wsDel.Delete

to

On Error Resume Next
Set wsDel = Sheets("Matching Contacts")
On Error GoTo 0
If Not wsDel Is Nothing Then wsDel.Delete

line On Error GoTo 0 will return your error handler to default mode.

Some issues with your code:

1) In line If Not LocatedCell = "Nothing" Then you tries to identify whether your cells value doesn’t equal string “Nothing” which is uncorrect.

To check whether the .Find function returns any cell, change 

If Not LocatedCell = "Nothing" Then

to 

If Not LocatedCell Is Nothing Then

2) change With Sheets(mailerSheet).Range("A:A") to With
mailerSheet.Range("A:A")

3) as @SiddharthRout mentioned in comments below,

The copy has to be right before the paste special. Excel is very well known for clearing the clipboard if you perform some specific actions

if you are going to change interior color and copy row, change 

'With_
   LocatedCell.EntireRow.Copy_
   '.Interior.ColorIndex = 4 'green
'End With

to

With LocatedCell.EntireRow
     .Interior.ColorIndex = 4 'green
     .Copy
End With

4) and of course: How to avoid using Select/Active statements

More Related Contents:

  1. Loop through files in a folder using VBA?
  2. How to error handle 1004 Error with WorksheetFunction.VLookup?
  3. How can Excel VBA open file using default application
  4. How to compare two entire rows in a sheet
  5. Run same excel macro on multiple excel files
  6. how to detect whether VBA excel found something?
  7. VBA : save a file with UTF-8 without BOM
  8. Excel tab sheet names vs. Visual Basic sheet names
  9. Excel VBA “Method ‘Document’ of object ‘IWebBrowser2’ failed”
  10. Determine if cell contains data validation
  11. VBA – Range to jpg picture
  12. Fastest way to delete rows which cannot be grabbed with SpecialCells
  13. Combine multiple Excel workbooks into a single workbook
  14. Excel Macro, inserting internationally valid formula during run-time
  15. Sheet protection: UserInterFaceOnly gone
  16. VBScript – How to make program wait until process has finished?
  17. Making VBA Form TextBox accept Numbers only (including +, – and .)
  18. VBA Convert date to week number
  19. Inserting an Online Picture to Excel with VBA
  20. Change length of For loop while in the loop
  21. VBA – Run Time Error 1004 ‘Application Defined or Object Defined Error’
  22. Consolidating worksheets into one
  23. Copy filtered data to another sheet using VBA
  24. TextJoin UDF For Excel 2013
  25. Open an Excel file from SharePoint site
  26. How to get address of cell which calls a VBA Functions in a Excel sheet
  27. Add new row to excel Table (VBA)
  28. How do I open a file if I only know part of the file name?
  29. VBA recognizing workbook by partial name
  30. VBA inheritance, analog of super

Leave a Comment