When Testing, VBA code works, When run normally, It doesn't

Question

Code that searches for duplicates works in test mode, but doesn't when I run it normally.

I have a subroutine called FindDuplicatesInColumn, placed in the section that Validates data entered into a userform.

' 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

The Subroutine I created

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

I am trying to check when the user enters the EmployeeNo1 in the userform. It is checked against the data in Column B of Data_Input to see if it is duplicated. If it is, then to stop the data from the form being added to the database.

https://stackoverflow.com/questions/17733541/why-does-range-work-but-not-cells — Siddharth Rout, Oct 20 '20 at 16:48

score 2 · Accepted Answer · answered Oct 20 '20 at 16:22

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

should be

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

...otherwise that second Range() defaults to the active sheet and not necessarily to the sheet you want.

When Testing, VBA code works, When run normally, It doesn't

1 Answers1