Creating a vba code to show a message not to appear duplicates in some sheets-CodePudding

Currently i have this code to register daily all meals at a canteen.

  Sub register()
  Dim s As Worksheet
  Set s = Worksheets("Lista_" & Range("K9").Value)
  Dim row As Long
  row = s.Cells(s.Rows.Count, "B").End(xlUp).row   1
  s.Cells(row, "B").Value = Range("C7").Value
  s.Cells(row, "C").Value = Range("C9").Value
  s.Cells(row, "H").Value = Range("L9").Value
  s.Cells(row, "I").Value = Range("P20").Value
  s.Cells(row, "N").Value = Range("P21").Value
  s.Cells(row, "O").Value = Range("P1").Value
  Range("M6:M19").Select
 Range("M19").Activate
Selection.ClearContents
Range("C7:D7").Select
Selection.ClearContents
Range("C7").Select
End Sub

I would like there to be a message if an employee's number has already been registered (so as not to duplicate it)... for that the vba code should search on all pages if that number already existed or not.If the number appears in column B of sheets that begin with "Lista_" a message should appear

CodePudding user response：

I think you just need to add a function that checks for the employee number

Something like this worked for me using your sample data

You can change constants and data types to match your situation

Option Explicit

Sub register()
    Dim s As Worksheet
    Dim row As Long
    Dim employeeNum As String

    Set s = Worksheets("Lista_" & Range("K9").Value)
    
    row = s.Cells(s.Rows.Count, "B").End(xlUp).row   1
    employeeNum = Range("C7").Value

    If AlreadyRegistered(employeeNum) Then
        MsgBox "Ignoring Preexisting Employee Number: " & employeeNum
    Else
        s.Cells(row, "B").Value = employeeNum
        s.Cells(row, "C").Value = Range("C9").Value
        s.Cells(row, "H").Value = Range("L9").Value
        s.Cells(row, "I").Value = Range("P20").Value
        s.Cells(row, "N").Value = Range("P21").Value
        s.Cells(row, "O").Value = Range("P1").Value
        Range("M6:M19").Select
        Range("M19").Activate
        Selection.ClearContents
        Range("C7:D7").Select
        Selection.ClearContents
        Range("C7").Select
    End If
End Sub

Function AlreadyRegistered(employeeNum As String) As Boolean

    Const EmployeeColumn    As String = "B:B"

    Dim varSheets           As Variant
    Dim intSheet            As Integer
    Dim xlSheet             As Worksheet
    Dim rgeFound            As Range
    
    Dim i                   As Integer
    
    AlreadyRegistered = False
    varSheets = Array("Lista_AA", "Lista_BB", "Lista_CC")
    For intSheet = LBound(varSheets) To UBound(varSheets)
        Set xlSheet = Sheets(varSheets(i))
        Set rgeFound = xlSheet.Range(EmployeeColumn).Find(employeeNum)
        If Not (rgeFound Is Nothing) Then
            AlreadyRegistered = True
            Exit For
        End If
        
    Next intSheet

End Function