如果值*是*指定的，则不运行 [英] If Not function proceeds when value *is* the one specified

问题描述

我正在尝试编写一个脚本来检查另一个工作表中的重复值，但是我无法使其工作。在问题中，If函数始终继续进行，无论是否设置为如果不是或如果。

我确定这是一个明显的错误，但我无法理解。

  Sub mailer_followuptest（）
 
 Application.ScreenUpdating = False 
 
'从上一次运行中删除匹配的联系人数据
 Dim wsDel As Worksheet 
 Application.DisplayAlerts = False 
 Err.Clear 
错误恢复下一步
设置wsDel =表（匹配联系人）
 wsDel.Delete 
 
 Dim mailerSheet As Worksheet 
设置mailerSheet =工作表（调用数据）
 
设置MatchingContacts = Sheets.Add 
 MatchingContacts.Name =匹配联系人
 
 Dim DesiredEntry As String 
 Dim CRMContacts As Worksheet 
设置CRMContacts =工作表（CRM联系人）
 
 CRMContacts.Select 
范围（A1 ）。选择
 
 Do 
 ActiveCell.Offset（1，0）。选择
 DesiredEntry = ActiveCell.Value 
 
 
带表格mailerSheet）.Range（A：A）
 
 Dim LocatedCel l As Range 
 Set LocatedCell = .Find（what：= DesiredEntry，SearchOrder = = xlByRows，LookAt：= xlPart）
 
 
 
问题：如果不是位置= 没有然后
 
'With_ 
 LocatedCell.EntireRow.Copy_ 
'.Interior.ColorIndex = 4'green 
'End With 
 
 MatchingContacts.Select 
 Selection.PasteSpecial粘贴：= xlPasteValues，操作：= xlNone，SkipBlanks _ 
：= False，Transpose：= False 
 Application.CutCopyMode = False 
 ActiveCell。偏移（1，0）。选择
 
 
如果
 
结束
 
 CRMContacts.Select 
 
循环直到ActiveCell.Value =
 
 Application.ScreenUpdating = True 
 
 
 End Sub 
  

此外，我是否正确使用查找？

解决方案

使用 On Error Resume Next 明智地

不要使用对于整个代码的错误恢复下一个 - 它将隐藏所有你的错误。

使用 On Error Resume Next 表示将代码告知关闭并做你想要的。在大多数情况下，它会做你想要的...关闭和执行...但是你不会得到预期的结果或完全错误的结果，如下所示！ （SiddharthRout©：）

更改

  Err.Clear 
错误恢复下一步
设置wsDel =表（匹配联系人）
 wsDel.Delete 
  

to

  On Error Resume Next 
 Set wsDel = Sheets（Matching Contacts ）
错误GoTo 0 
如果不是wsDel是没有，然后wsDel.Delete 
  

行 On Error GoTo 0 将会将您的错误处理程序返回到默认模式。

---

您的代码有些问题：

1）在 Not LocatedCell =Nothing然后您尝试识别您的单元格值是否不等于Nothing，这是不正确的。 p>

要检查 .Find 函数是否返回任何单元格，请更改

 如果不在位置=没有然后
  

到

 如果不是位置不是没有
  

2）更改 With Sheets（mailerSheet）.Range（A：A） b $ b mailerSheet.Range（A：A）

3）在评论中提到的@SiddharthRout以下，

该副本必须在粘贴特殊之前。如果您执行某些特定操作，Excel非常有效，如果要更改内部颜色和复制行，请执行某些特定操作

更改

 'With_ 
 LocatedCell.EntireRow.Copy_ 
'.Interior.ColorIndex = 4'green 
'结束与
  

到

  WithCell.EntireRow 
 .Interior.ColorIndex = 4'green 
 .Copy 
结束
  / pre> 
 
 
  4），当然：如何避免使用Select / Active语句 
 
I am trying to write a script which checks for duplicate values in another worksheet, but I cannot get it to work. At line problem the If function always proceeds, whether set to If Not or If. LocatedCell does equal Nothing.

I am sure this is an obvious error but I cannot understand it.
Sub mailer_followuptest()

Application.ScreenUpdating = False

'Remove matching contacts data from last run
   Dim wsDel As Worksheet
    Application.DisplayAlerts = False
    Err.Clear
    On Error Resume Next
    Set wsDel = Sheets("Matching Contacts")
    wsDel.Delete

Dim mailerSheet As Worksheet
Set mailerSheet = Worksheets("Call data")

Set MatchingContacts = Sheets.Add
MatchingContacts.Name = "Matching Contacts"

Dim DesiredEntry As String
Dim CRMContacts As Worksheet
Set CRMContacts = Worksheets("CRM contacts")

CRMContacts.Select
Range("A1").Select

Do
ActiveCell.Offset(1, 0).Select
DesiredEntry = ActiveCell.Value


    With Sheets(mailerSheet).Range("A:A")

        Dim LocatedCell As Range
        Set LocatedCell = .Find(What:=DesiredEntry, SearchOrder:=xlByRows, LookAt:=xlPart)



problem: If Not LocatedCell = "Nothing" Then

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

        MatchingContacts.Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Application.CutCopyMode = False
        ActiveCell.Offset(1, 0).Select


        End If

    End With

    CRMContacts.Select

Loop Until ActiveCell.Value = ""

Application.ScreenUpdating = True


End Sub
Additionally, am I using find correctly? It doesn't appear to be working either.
解决方案
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 ©:)



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

                        
这篇关于如果值*是*指定的，则不运行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！