问题描述
我正在尝试编写一个脚本来检查另一个工作表中的重复值,但是我无法使其工作。在问题
中,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以下,
更改
'With_
LocatedCell.EntireRow.Copy_
'.Interior.ColorIndex = 4'green
'结束与
到
WithCell.EntireRow
/ pre>
.Interior.ColorIndex = 4'green
.Copy
结束
4),当然:
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 toIf Not
orIf
. 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 toShut 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, changeIf Not LocatedCell = "Nothing" Then
to
If Not LocatedCell Is Nothing Then
2) change
With Sheets(mailerSheet).Range("A:A")
toWith mailerSheet.Range("A:A")
3) as @SiddharthRout mentioned in comments below,
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
这篇关于如果值*是*指定的,则不运行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!