测试时，VBA代码有效，正常运行时，它不起作用

Question

搜索重复项的代码在测试模式下有效，但在正常运行时无效。

我有一个名为FindDuplicatesInColumn的子程序，放在验证输入到用户表单中的数据的部分。

' Check if all data has been entered on the userform
Function Data_Validation() As Boolean 'Declare Function with Bool as data type
'Default True. False if any conditions met. When a function is called, a new variable,
'with the function name and datatype given is created.  You'll set the value in the
'function.  When the function ends either in Exit Function or
'End Function, whatever is contained in this variable is returned as the Functions result
Data_Validation = True
' Check if all data has been entered on the userform
If ARLArea = False And KNBArea = False And LSQArea = False And RSQArea = False And RevenueControlInspectors = False And SpecialRequirementTeam = False Then
MsgBox "Select Area!", vbInformation, ("Area")
ARLArea.SetFocus
Data_Validation = False
Exit Function
End If
If EmployeeNo1 = "" Then
MsgBox "Enter Employee Number!", vbInformation, ("Employee Number")
EmployeeNo1.SetFocus
Data_Validation = False
Exit Function
End If
If FirstName1 = "" Then
MsgBox "Enter First Name!", vbInformation, ("First Name")
FirstName1.SetFocus
Data_Validation = False
Exit Function
End If
If LastName1 = "" Then
MsgBox "Enter Last Name!", vbInformation, ("Last Name")
LastName1.SetFocus
Data_Validation = False
Exit Function
End If
If CSA2 = False And CSA1 = False And CSS2 = False And CSS1 = False And CSM2 = False And CSM1 = False And AM = False And RCI = False And SRT = False Then
MsgBox "Select Grade!", vbInformation, ("Grade")
CSA2.SetFocus
Data_Validation = False
Exit Function
End If

BlnVal = 1

FindDuplicatesInColumn
End Function

我创建的子程序

Sub FindDuplicatesInColumn()
Dim sAccNum As String
Dim rAccLst As Range
Dim res
Dim IdVal As Integer
Set rAccLst = Sheets("Data Input").Range("B5:B" & Range("B" & Rows.Count).End(xlUp).Row) 
sAccNum = EmployeeNo1
If Not rAccLst.Find(What:=sAccNum, LookIn:=xlValues, Lookat:=xlPart) Is Nothing Then
MsgBox "Sorry, This person already exists in the Database!"
'Empty Area
ARLArea = False
LSQArea = False
KNBArea = False
RSQArea = False
RevenueControlInspectors = False
SpecialRequirementTeam = False

'Empty EmployeeNo1
EmployeeNo1.Value = ""
'Empty FirstName1
FirstName1.Value = ""

'Empty LastName1
LastName1.Value = ""

'Empty Grade
CSA2 = False
CSA1 = False
CSS2 = False
CSS1 = False
CSM2 = False
CSM1 = False
AM = False
RCI = False
SRT = False

sAccNum = ""
If Data_Validation() = False Then
Exit Sub
End If
Else
'Sheets("Data Input").Range("B" & Range("B" & Rows.Count).End(xlUp).Row).Offset(1, 0).Value = "ACC" & sAccNum
End If

End Sub

我正在尝试检查用户何时在用户表单中输入EmployeeNo1。对照Data_Input的B列中的数据检查是否重复。如果是，则停止将表单中的数据添加到数据库中。

Answer 1

Set rAccLst = Sheets("Data Input").Range("B5:B" & Range("B" & Rows.Count).End(xlUp).Row) 

应该是

With Sheets("Data Input")
Set rAccLst = .Range("B5:B" & .Range("B" & .Rows.Count).End(xlUp).Row) 
End with

否则，第二个Range((默认为活动工作表，而不一定是所需的工作表。