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

Question

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

You're only referencing one worksheet starting with lista. Are you saying there's multiple worksheets with same prefix in your workbook? — dbmitch, Apr 01 '22 at 16:10
Yes! These code register the employee acordding company: if the employee belongs to the AA company, then register the employee in "Lista_AA"; if belongs to the BB company, then register the employee in "Lista_BB"... (I have 3 sheets: Lista_AA, Lista_BB and Lista_CC — Beatriz, Apr 01 '22 at 21:58
Please check my Layout at my other question: https://stackoverflow.com/questions/71574151/how-can-i-record-conditional-data-in-a-list-with-vba-code/71579405?noredirect=1#comment126509269_71579405 — Beatriz, Apr 01 '22 at 22:03

score 1 · Accepted Answer · answered Apr 01 '22 at 23:22

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

Good afternoon, the message box didn't apear and these code not function... — Beatriz, Apr 04 '22 at 16:10
I assume the code functions if you're not reporting an error. Have you tried to step through the function and debug.print values to troubleshoot? Help yourself by helping us. — dbmitch, Apr 04 '22 at 16:22
Everything works!! Thanks for the help! In the afternoon when I asked, the error was a simple spelling mistake that caused the debug — Beatriz, Apr 04 '22 at 21:09
I am creating a question that came to me following the construction of this canteen data recording process. If you want to help me soon I'll put the link here... thanks — Beatriz, Apr 04 '22 at 21:36
please check my other question: https://stackoverflow.com/questions/71744152/how-to-update-daily-data-in-monthly-data-table-with-vba-code Many thanks!! — Beatriz, Apr 04 '22 at 21:54

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

1 Answers1