Sub ValidateAllSheets()
    Dim wbModel As Workbook
    Dim wbData As Workbook
    Dim wsData As Worksheet
    Dim sheetName As String
    Dim report As String
    Dim modelFile As String
    Dim dataFile As String
    Dim reportFile As String
    
    ' Ask the user to select the model file and data file
    modelFile = Application.GetOpenFilename("Excel Files (*.xlsx), *.xlsx", , "Sélectionnez le fichier modèle")
    If modelFile = "Faux" Then Exit Sub
    
    dataFile = Application.GetOpenFilename("Excel Files (*.xlsx), *.xlsx", , "Sélectionnez le fichier de données")
    If dataFile = "Faux" Then Exit Sub
    
    ' Open the files and set to workbook objects
    Set wbModel = Workbooks.Open(modelFile)
    Set wbData = Workbooks.Open(dataFile)
    
    ' Initialize the report
    report = "Rapport de validation des données :" & vbCrLf & vbCrLf

    ' Loop through each sheet in the data file
    For Each wsData In wbData.Sheets
        sheetName = wsData.Name
        ' Perform validations based on the sheet name
        Select Case sheetName

            Case "SOURCE MANUSCRITE"
                Call ValidatePublicationOrProductionDate(wsData, "Date de production", sheetName, report, False)
                Call ValidatePublicationOrProductionDate(wsData, "Date de modification (dernière révision dans le cas de retouches multiples)", sheetName, report, False)
                Call ValidateReferencedIDs(wsData, "ID Source imprimée (optionnel)", wsData, "ID", sheetName, "SOURCE IMPRIMEE", report)
            Case "PERSONNE"
                Call ValidateDates(wsData, "Date de naissance", "Date de décès", sheetName, report)
                Call ValidateIDPrefix(wsData, "ID Personne (optionnel)", "person", sheetName, report)
                Call CheckForDuplicates(wsData, Array("Nom", "Prénom"), sheetName, report)
                Call ValidateReferencedIDs(wsData, "ID Source manuscrite (optionnel)", wsData, "ID", sheetName, "SOURCE MANUSCRITE", report)
            Case "OEUVRE"
                Call ValidatePublicationOrProductionDate(wsData, "Date de création ou publication", sheetName, report, True)
                Call CheckForDuplicates(wsData, Array("Titre ou [Titre forgé] + sous-titre"), sheetName, report)
                Call ValidateIDPrefix(wsData, "ID Oeuvre (optionnel)", "work", sheetName, report)
            Case "SOURCE IMPRIMÉE"
                Call ValidateYearFormat(wsData, "Année d'édition", sheetName, report)
                Call ValidateFormatPliage(wsData, "Format réel (pliage)", sheetName, report)
                Call ValidateReferencedIDs(wsData, "ID Personne (Optionnel)", wsData, "ID", sheetName, "PERSONNE", report)
                Call ValidateReferencedIDs(wsData, "ID Collectivité", wsData, "ID", sheetName, "COLLECTIVITÉ", report)
            Case "LIEU"
                Call ValidateEventDates(wsData, "Date de début (optionnel)", "Date de fin (optionnel)", sheetName, report)
                Call ValidateIDPrefix(wsData, "ID Lieu", "place", sheetName, report)
                Call CheckForDuplicates(wsData, Array("Label"), sheetName, report)
            Case "COLLECTIVITÉ"
                Call ValidatePeriod(wsData, "Période d'activité (Optionnel)", sheetName, report)
                Call ValidateIDPrefix(wsData, "ID Collectivité", "group", sheetName, report)
                Call ValidateReferencedIDs(wsData, "ID Lieu", wsData, "ID", sheetName, "LIEU", report)
            Case "DÉPÔT"
                Call ValidateIDPrefix(wsData, "ID Dépôt", "repo", sheetName, report)
                Call CheckForDuplicates(wsData, Array("Label"), sheetName, report)
                Call ValidateReferencedIDs(wsData, "ID Collectivité", wsData, "ID", sheetName, "COLLECTIVITÉ", report)
            Case "EVENEMENT"
                Call ValidateEventDates(wsData, "Date ou date de début", "Date de fin", sheetName, report)
                Call CheckForDuplicates(wsData, Array("Titre"), sheetName, report)
                Call ValidateIDPrefix(wsData, "ID Evénement (Optionnel)", "event", sheetName, report)
        End Select
    Next wsData

    ' Output the validation report to a text file
    reportFile = Application.DefaultFilePath & "\rapport_erreurs.txt"
    Open reportFile For Output As #1
    Print #1, report
    Close #1
    
    ' Display completion message
    MsgBox "Validation terminée. Rapport généré : " & reportFile
    
    ' Close the files properly
    wbModel.Close False
    wbData.Close False
End Sub

Sub CheckForDuplicates(ws As Worksheet, columnNames As Variant, sheetName As String, ByRef report As String)
    Dim lastRow As Long, i As Long, key As String
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    
    ' Find the last row of data
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    ' Loop through the rows and check for duplicates
    For i = 2 To lastRow
        key = ""
        ' Concatenate column values to form a key for checking duplicates
        For Each col In columnNames
            key = key & ws.Cells(i, col).value & " "
        Next col
        
        ' Check if the key is already in the dictionary
        If dict.exists(Trim(key)) Then
            report = report & "Les valeurs '" & Trim(key) & "' sont dupliquées dans la feuille '" & sheetName & "' à la ligne " & i & "." & vbCrLf
        Else
            dict.Add Trim(key), i
        End If
    Next i
End Sub

Sub ValidateURLs(ws As Worksheet, urlColumnName As String, sheetName As String, ByRef report As String)
    Dim lastRow As Long, i As Long
    Dim regex As Object
    Set regex = CreateObject("VBScript.RegExp")
    regex.Pattern = "^https?://[^\s]+$"  ' Simple URL regex pattern
    regex.IgnoreCase = True
    
    ' Find the last row of data
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    ' Loop through each row to check the URLs
    For i = 2 To lastRow
        If Not IsEmpty(ws.Cells(i, ws.Columns(urlColumnName).Column).value) Then
            If Not regex.Test(ws.Cells(i, ws.Columns(urlColumnName).Column).value) Then
                report = report & "Le lien URL '" & ws.Cells(i, ws.Columns(urlColumnName).Column).value & "' à la ligne " & i & " dans la feuille '" & sheetName & "' n'est pas valide." & vbCrLf
            End If
        End If
    Next i
End Sub

Sub ValidateEventDates(ws As Worksheet, startDateColumn As String, endDateColumn As String, sheetName As String, ByRef report As String)
    Dim lastRow As Long, i As Long
    Dim startDate As Date, endDate As Date
    
    ' Find the last row of data
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    ' Loop through the rows to validate start and end dates
    For i = 2 To lastRow
        On Error Resume Next
        startDate = CDate(ws.Cells(i, ws.Columns(startDateColumn).Column).value)
        endDate = CDate(ws.Cells(i, ws.Columns(endDateColumn).Column).value)
        On Error GoTo 0
        
        If startDate > endDate Then
            report = report & "La date de début '" & startDate & "' est après la date de fin '" & endDate & "' à la ligne " & i & " dans la feuille '" & sheetName & "'." & vbCrLf
        End If
    Next i
End Sub

Sub ValidateIdentifiers(ws As Worksheet, idColumn As String, sheetName As String, expectedPrefix As String, ByRef report As String)
    Dim lastRow As Long, i As Long
    Dim idValue As String
    
    ' Find the last row of data
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    ' Loop through each row to check identifiers
    For i = 2 To lastRow
        idValue = ws.Cells(i, ws.Columns(idColumn).Column).value
        
        ' Check if the ID has the expected prefix
        If Left(idValue, Len(expectedPrefix)) <> expectedPrefix Then
            report = report & "L'identifiant '" & idValue & "' à la ligne " & i & " dans la colonne '" & idColumn & "' de la feuille '" & sheetName & "' ne commence pas par le préfixe attendu '" & expectedPrefix & "'." & vbCrLf
            ' Colorer la cellule en rouge pour indiquer l'erreur
            ws.Cells(i, ws.Columns(idColumn).Column).Interior.Color = RGB(255, 0, 0)
        End If
    Next i
End Sub

Sub ValidateContributorInitials(ws As Worksheet, contributorColumn As String, initialsColumn As String, sheetName As String, ByRef report As String)
    Dim lastRow As Long, i As Long
    Dim contributorValue As String, initialsValue As String
    
    ' Find the last row of data
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    ' Loop through each row to check initials against contributor names
    For i = 2 To lastRow
        contributorValue = ws.Cells(i, ws.Columns(contributorColumn).Column).value
        initialsValue = ws.Cells(i, ws.Columns(initialsColumn).Column).value
        
        ' Check if the initials match the contributor name's expected format
        If Not UCase(initialsValue) Like UCase(Left(contributorValue, 2)) Then
            report = report & "Les initiales '" & initialsValue & "' à la ligne " & i & " dans la colonne '" & initialsColumn & "' de la feuille '" & sheetName & "' ne correspondent pas au nom '" & contributorValue & "'." & vbCrLf
            ' Colorer la cellule en rouge pour indiquer l'erreur
            ws.Cells(i, ws.Columns(initialsColumn).Column).Interior.Color = RGB(255, 0, 0)
        End If
    Next i
End Sub

' Fonction pour trouver le numéro de colonne à partir du nom de la colonne
Function GetColumnNumbers(ws As Worksheet, colName As String) As Long
    Dim cell As Range
    For Each cell In ws.Rows(1).Cells
        If Trim(cell.value) = Trim(colName) Then
            GetColumnNumbers = cell.Column
            Exit Function
        End If
    Next cell
    GetColumnNumbers = -1 ' Retourner -1 si la colonne n'est pas trouvée
End Function

Sub ValidatePublicationOrProductionDate(ws As Worksheet, dateColumn As String, sheetName As String, ByRef report As String, allowRelative As Boolean)
    Dim lastRow As Long
    Dim i As Long
    Dim dateValue As String
    Dim colNumber As Long
    Dim regexFullDate As Object
    Dim regexMonthYear As Object
    Dim regexYear As Object
    Dim regexCircaBefore As Object
    Dim regexCircaAfter As Object
    Dim regexBeforeYear As Object
    Dim regexYearRange As Object
    Dim regexYearInterval As Object

    ' Create regex patterns for different date formats
    Set regexFullDate = CreateObject("VBScript.RegExp")
    regexFullDate.Pattern = "^\d{2}/\d{2}/\d{4}$"  ' Full date: dd/mm/yyyy
    
    Set regexMonthYear = CreateObject("VBScript.RegExp")
    regexMonthYear.Pattern = "^\d{2}/\d{4}$"  ' Month/Year: mm/yyyy
    
    Set regexYear = CreateObject("VBScript.RegExp")
    regexYear.Pattern = "^\d{4}$"  ' Year: yyyy
    
    ' If relative formats are allowed
    If allowRelative Then
        Set regexCircaBefore = CreateObject("VBScript.RegExp")
        regexCircaBefore.Pattern = "^ca \d{4}$"  ' Circa before: ca 1750
        
        Set regexCircaAfter = CreateObject("VBScript.RegExp")
        regexCircaAfter.Pattern = "^\d{4} ca\.$"  ' Circa after: 1750 ca.
        
        Set regexBeforeYear = CreateObject("VBScript.RegExp")
        regexBeforeYear.Pattern = "^\d{4} av\.$"  ' Before year: 1750 av.
        
        Set regexYearRange = CreateObject("VBScript.RegExp")
        regexYearRange.Pattern = "^\d{4}-\d{4}$"  ' Year range: 1750-1751
        
        Set regexYearInterval = CreateObject("VBScript.RegExp")
        regexYearInterval.Pattern = "^\d{4} ; \d{4}$"  ' Year interval: 1750 ; 1751
    End If

    ' Find the last row with data
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    ' Get the column number for the date column
    colNumber = GetColumnNumbers(ws, dateColumn)

    ' Loop through each row to validate the date
    For i = 2 To lastRow
        dateValue = Trim(ws.Cells(i, colNumber).value)
        
        ' Skip empty values
        If dateValue = "" Then
            GoTo NextRow
        End If
        
        ' Check for valid formats
        If Not IsNumeric(dateValue) And Not regexFullDate.Test(dateValue) And Not regexMonthYear.Test(dateValue) And Not regexYear.Test(dateValue) Then
            If Not allowRelative Or (allowRelative And Not (regexCircaBefore.Test(dateValue) Or regexCircaAfter.Test(dateValue) Or regexBeforeYear.Test(dateValue) Or regexYearRange.Test(dateValue) Or regexYearInterval.Test(dateValue))) Then
                ' Report invalid date
                report = report & "Erreur : La valeur '" & dateValue & "' à la ligne " & i & " dans la colonne '" & dateColumn & "' de la feuille '" & sheetName & "' n'est pas une date valide." & vbCrLf
                ' Highlight the invalid cell in red
                ws.Cells(i, colNumber).Interior.Color = RGB(255, 0, 0)
            End If
        End If
        
NextRow:
    Next i
End Sub


Sub ValidateReversePersonRelationships(ws As Worksheet, idColumn As String, relationColumn As String, sheetName As String, ByRef report As String)
    Dim lastRow As Long, i As Long, j As Long
    Dim personID As String, relatedPersonID As String
    Dim reverseFound As Boolean
    Dim idDict As Object
    Set idDict = CreateObject("Scripting.Dictionary")
    
    ' Find the last row of data
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    ' Load all person IDs into a dictionary
    For i = 2 To lastRow
        personID = Trim(ws.Cells(i, ws.Columns(idColumn).Column).value)
        If personID <> "" Then
            idDict.Add personID, i
        End If
    Next i
    
    ' Loop through each row to check reverse relationships
    For i = 2 To lastRow
        personID = Trim(ws.Cells(i, ws.Columns(idColumn).Column).value)
        relatedPersonID = Trim(ws.Cells(i, ws.Columns(relationColumn).Column).value)
        
        If relatedPersonID <> "" Then
            relatedPersons = Split(relatedPersonID, ";")
            
            ' Loop through each related person
            For j = LBound(relatedPersons) To UBound(relatedPersons)
                relatedPerson = Trim(relatedPersons(j))
                
                If idDict.exists(relatedPerson) Then
                    ' Check if the related person's row contains the current personID in reverse
                    reverseFound = False
                    relatedPersonRow = idDict(relatedPerson)
                    relatedPersonRelationValue = ws.Cells(relatedPersonRow, ws.Columns(relationColumn).Column).value
                    
                    If InStr(relatedPersonRelationValue, personID) > 0 Then
                        reverseFound = True
                    End If
                    
                    ' If the reverse relationship is not found, log an error
                    If Not reverseFound Then
                        report = report & "La personne '" & personID & "' dans la ligne " & i & " est liée à '" & relatedPerson & "', mais la relation inverse est manquante dans la ligne " & relatedPersonRow & "." & vbCrLf
                        ' Colorer la cellule en rouge pour indiquer l'erreur
                        ws.Cells(i, ws.Columns(relationColumn).Column).Interior.Color = RGB(255, 0, 0)
                    End If
                Else
                    report = report & "Erreur: La personne '" & relatedPerson & "' à la ligne " & i & " dans la colonne '" & relationColumn & "' de la feuille '" & sheetName & "' n'existe pas dans la colonne '" & idColumn & "'." & vbCrLf
                End If
            Next j
        End If
    Next i
End Sub


Sub ValidateFormatColumn(ws As Worksheet, formatColumn As String, sheetName As String, ByRef report As String)
    Dim lastRow As Long, i As Long
    Dim formatValue As String
    Dim allowedFormats As Variant
    allowedFormats = Array("in 4°", "in 8°", "in 16°", "in 32°")
    
    ' Find the last row of data
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    ' Loop through each row to check the format
    For i = 2 To lastRow
        formatValue = ws.Cells(i, ws.Columns(formatColumn).Column).value
        
        ' Check if the format value is allowed
        If IsError(Application.Match(formatValue, allowedFormats, 0)) Then
            report = report & "Le format '" & formatValue & "' à la ligne " & i & " dans la colonne '" & formatColumn & "' de la feuille '" & sheetName & "' n'est pas un format reconnu." & vbCrLf
            ' Colorer la cellule en rouge pour indiquer l'erreur
            ws.Cells(i, ws.Columns(formatColumn).Column).Interior.Color = RGB(255, 0, 0)
        End If
    Next i
End Sub

Sub ValidatePersonNames(ws As Worksheet, personSheet As Worksheet, personColumns As Variant, sheetName As String, ByRef report As String)
    Dim lastRow As Long, i As Long, personLastRow As Long
    Dim personName As String, personFirstName As String, combinedPersonName As String
    Dim personDict As Object
    Set personDict = CreateObject("Scripting.Dictionary")
    
    ' Load all names from the PERSONNE sheet into a dictionary
    personLastRow = personSheet.Cells(personSheet.Rows.Count, 1).End(xlUp).Row
    For i = 2 To personLastRow
        personName = personSheet.Cells(i, 1).value
        personFirstName = personSheet.Cells(i, 2).value
        combinedPersonName = Trim(personFirstName) & " " & Trim(personName)
        personDict.Add combinedPersonName, i
    Next i
    
    ' Find the last row of data in the current sheet
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    ' Loop through each row to check person names
    For i = 2 To lastRow
        For Each col In personColumns
            If Not personDict.exists(Trim(ws.Cells(i, col).value)) Then
                report = report & "Le nom '" & ws.Cells(i, col).value & "' à la ligne " & i & " dans la colonne '" & col & "' de la feuille '" & sheetName & "' ne correspond à aucun nom de la feuille PERSONNE." & vbCrLf
                ' Colorer la cellule en rouge pour indiquer l'erreur
                ws.Cells(i, col).Interior.Color = RGB(255, 0, 0)
            End If
        Next col
    Next i
End Sub

' Fonction pour valider les années au format AAAA
Sub ValidateYearFormat(ws As Worksheet, yearColumn As String, sheetName As String, ByRef report As String)
    Dim lastRow As Long
    Dim yearValue As String
    Dim i As Long
    Dim colNumber As Long
    Dim regexYear As Object

    ' Créer l'objet regex pour l'année au format AAAA
    Set regexYear = CreateObject("VBScript.RegExp")
    regexYear.Pattern = "^\d{4}$"

    ' Trouver la dernière ligne avec des données
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    ' Trouver le numéro de colonne correspondant au nom de colonne
    colNumber = GetColumnNumbers(ws, yearColumn)

    ' Boucle pour valider chaque année dans la colonne
    For i = 2 To lastRow
        yearValue = Trim(ws.Cells(i, colNumber).value)

        ' Vérifier si la cellule est vide ou contient "nan"
        If yearValue = "" Or yearValue = "nan" Then
            ' Ignorer les valeurs vides
        ElseIf regexYear.Test(yearValue) Then
            ' Année valide
        Else
            ' Ajouter l'erreur au rapport
            report = report & "Erreur : L'année '" & yearValue & "' à la ligne " & i & " dans la colonne '" & yearColumn & "' de la feuille '" & sheetName & "' n'est pas au format AAAA." & vbCrLf
            ' Colorer la cellule en rouge pour indiquer l'erreur
            ws.Cells(i, colNumber).Interior.Color = RGB(255, 0, 0)
        End If
    Next i
End Sub

Sub ValidatePeriod(ws As Worksheet, periodColumn As String, sheetName As String, ByRef report As String)
    Dim lastRow As Long
    Dim i As Long
    Dim periodValue As String
    Dim dates As Variant
    Dim startYear As Long, endYear As Long
    
    ' Find the last row of data
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    ' Loop through each row to validate the period
    For i = 2 To lastRow
        periodValue = Trim(ws.Cells(i, ws.Columns(periodColumn).Column).value)
        
        ' Handle missing or empty values
        If periodValue = "" Then
            ' Skip if the period is missing or empty
            GoTo NextRow
        End If
        
        ' Split the period by '-'
        dates = Split(periodValue, "-")
        If UBound(dates) = 1 Then
            On Error Resume Next
            startYear = CLng(Trim(dates(0)))
            endYear = CLng(Trim(dates(1)))
            On Error GoTo 0
            
            ' Check if start year is after end year
            If startYear > endYear Then
                report = report & "La période '" & periodValue & "' à la ligne " & i & " dans la feuille '" & sheetName & "' a une date de début après la date de fin." & vbCrLf
                ' Color the cell in red to indicate the error
                ws.Cells(i, ws.Columns(periodColumn).Column).Interior.Color = RGB(255, 0, 0)
            End If
        ElseIf Not IsNumeric(periodValue) Or Len(periodValue) <> 4 Then
            ' Check if the period is not a valid year (must be a 4-digit year)
            report = report & "La période '" & periodValue & "' à la ligne " & i & " dans la feuille '" & sheetName & "' n'est pas au bon format." & vbCrLf
            ' Color the cell in red to indicate the error
            ws.Cells(i, ws.Columns(periodColumn).Column).Interior.Color = RGB(255, 0, 0)
        End If
        
NextRow:
    Next i
End Sub

Sub ValidateDates(ws As Worksheet, birthCol As String, deathCol As String, sheetName As String, ByRef report As String)
    Dim lastRow As Long
    Dim i As Long
    Dim birthDate As Variant
    Dim deathDate As Variant
    
    ' Find the last row with data
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    ' Loop through each row to validate birth and death dates
    For i = 2 To lastRow
        birthDate = ws.Cells(i, ws.Columns(birthCol).Column).Value
        deathDate = ws.Cells(i, ws.Columns(deathCol).Column).Value
        
        ' Check if birthDate is a valid date
        If Not IsDate(birthDate) Then
            report = report & "Erreur : La valeur '" & birthDate & "' à la ligne " & i & " dans la colonne '" & birthCol & "' de la feuille '" & sheetName & "' n'est pas une date valide." & vbCrLf
            ws.Cells(i, ws.Columns(birthCol).Column).Interior.Color = RGB(255, 0, 0) ' Color the invalid cell in red
        End If
        
        ' Check if deathDate is a valid date
        If Not IsDate(deathDate) Then
            report = report & "Erreur : La valeur '" & deathDate & "' à la ligne " & i & " dans la colonne '" & deathCol & "' de la feuille '" & sheetName & "' n'est pas une date valide." & vbCrLf
            ws.Cells(i, ws.Columns(deathCol).Column).Interior.Color = RGB(255, 0, 0) ' Color the invalid cell in red
        End If
        
        ' If both dates are valid, compare birthDate and deathDate
        If IsDate(birthDate) And IsDate(deathDate) Then
            On Error Resume Next ' Avoid errors when converting invalid dates
            If CDate(birthDate) >= CDate(deathDate) Then
                report = report & "Erreur : La date de naissance à la ligne " & i & " est après la date de décès dans la feuille '" & sheetName & "'." & vbCrLf
                ws.Cells(i, ws.Columns(birthCol).Column).Interior.Color = RGB(255, 0, 0) ' Color the birth cell in red
                ws.Cells(i, ws.Columns(deathCol).Column).Interior.Color = RGB(255, 0, 0) ' Color the death cell in red
            End If
            On Error GoTo 0 ' Reset error handling
        End If
    Next i
End Sub


Sub ValidateRestrictedValues(ws As Worksheet, columnName As String, validValues As Variant, sheetName As String, ByRef report As String)
    Dim lastRow As Long, i As Long
    Dim value As String
    
    ' Find the last row of data
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    ' Loop through each row to check if the value is valid
    For i = 2 To lastRow
        value = ws.Cells(i, ws.Columns(columnName).Column).value
        
        ' Check if the value is part of the valid values array
        If IsError(Application.Match(value, validValues, 0)) Then
            report = report & "La valeur '" & value & "' à la ligne " & i & " dans la colonne '" & columnName & "' de la feuille '" & sheetName & "' n'est pas une valeur valide." & vbCrLf
            ' Highlight the cell in red
            ws.Cells(i, ws.Columns(columnName).Column).Interior.Color = RGB(255, 0, 0)
        End If
    Next i
End Sub

Sub ValidateGenre(ws As Worksheet, columnName As String, sheetName As String, validGenres As Variant, ByRef report As String)
    Dim lastRow As Long, i As Long
    Dim genre As String
    
    ' Find the last row of data
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    ' Loop through each row to validate the genre
    For i = 2 To lastRow
        genre = ws.Cells(i, ws.Columns(columnName).Column).value
        
        ' Check if the genre is part of the valid genres list
        If IsError(Application.Match(genre, validGenres, 0)) Then
            report = report & "Le genre '" & genre & "' à la ligne " & i & " dans la colonne '" & columnName & "' de la feuille '" & sheetName & "' n'est pas valide." & vbCrLf
            ' Highlight the cell in red
            ws.Cells(i, ws.Columns(columnName).Column).Interior.Color = RGB(255, 0, 0)
        End If
    Next i
End Sub

Sub CleanNaNValues(ws As Worksheet, columnName As String)
    Dim lastRow As Long, i As Long
    
    ' Find the last row of data
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    ' Loop through each row to clean NaN values
    For i = 2 To lastRow
        If ws.Cells(i, ws.Columns(columnName).Column).value = "nan" Then
            ws.Cells(i, ws.Columns(columnName).Column).value = ""
        End If
    Next i
End Sub

Sub ValidateFormatPliage(ws As Worksheet, columnName As String, sheetName As String, ByRef report As String)
    Dim lastRow As Long, i As Long
    Dim formatValue As String
    Dim allowedFormats As Variant
    allowedFormats = Array("in 4°", "in 8°", "in 16°", "in 32°")
    
    ' Find the last row of data
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    ' Loop through each row to validate the format
    For i = 2 To lastRow
        formatValue = ws.Cells(i, ws.Columns(columnName).Column).value
        
        ' Check if the format value is allowed
        If IsError(Application.Match(formatValue, allowedFormats, 0)) Then
            report = report & "Le format '" & formatValue & "' à la ligne " & i & " dans la colonne '" & columnName & "' de la feuille '" & sheetName & "' n'est pas valide." & vbCrLf
            ' Highlight the cell in red
            ws.Cells(i, ws.Columns(columnName).Column).Interior.Color = RGB(255, 0, 0)
        End If
    Next i
End Sub

Sub CleanSpaces(ws As Worksheet, columnName As String)
    Dim lastRow As Long, i As Long
    
    ' Find the last row of data
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    ' Loop through each row to clean spaces
    For i = 2 To lastRow
        ws.Cells(i, ws.Columns(columnName).Column).value = Trim(ws.Cells(i, ws.Columns(columnName).Column).value)
    Next i
End Sub

Sub ValidateUniqueIDs(ws As Worksheet, idColumn As String, sheetName As String, ByRef report As String)
    Dim lastRow As Long, i As Long
    Dim idValue As String
    Dim idDict As Object
    Set idDict = CreateObject("Scripting.Dictionary")
    
    ' Find the last row of data
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    ' Loop through each row to check for duplicate IDs
    For i = 2 To lastRow
        idValue = ws.Cells(i, ws.Columns(idColumn).Column).value
        
        ' Check if the ID already exists in the dictionary
        If idDict.exists(idValue) Then
            report = report & "L'identifiant '" & idValue & "' à la ligne " & i & " dans la colonne '" & idColumn & "' de la feuille '" & sheetName & "' est dupliqué." & vbCrLf
            ' Highlight the cell in red
            ws.Cells(i, ws.Columns(idColumn).Column).Interior.Color = RGB(255, 0, 0)
        Else
            idDict.Add idValue, i
        End If
    Next i
End Sub

Sub ValidateIDSequence(ws As Worksheet, idColumn As String, prefix As String, sheetName As String, ByRef report As String)
    Dim lastRow As Long, i As Long
    Dim idValue As String, expectedID As String
    Dim counter As Long
    
    ' Find the last row of data
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    ' Start counter for sequence
    counter = 1
    
    ' Loop through each row to check the ID sequence
    For i = 2 To lastRow
        idValue = ws.Cells(i, ws.Columns(idColumn).Column).value
        expectedID = prefix & counter
        
        ' Check if the ID matches the expected sequence
        If idValue <> expectedID Then
            report = report & "L'identifiant '" & idValue & "' à la ligne " & i & " dans la colonne '" & idColumn & "' de la feuille '" & sheetName & "' n'est pas dans la séquence attendue '" & expectedID & "'." & vbCrLf
            ' Highlight the cell in red
            ws.Cells(i, ws.Columns(idColumn).Column).Interior.Color = RGB(255, 0, 0)
        End If
        
        ' Increment the counter for the next expected ID
        counter = counter + 1
    Next i
End Sub

Sub ValidateMultipleIDsFormat(ws As Worksheet, idColumn As String, sheetName As String, ByRef report As String)
    Dim lastRow As Long, i As Long
    Dim idValue As String
    Dim idList As Variant
    Dim j As Long
    Dim regex As Object
    Set regex = CreateObject("VBScript.RegExp")
    regex.Pattern = "^[a-zA-Z]+\d+$"  ' Define the expected format for IDs
    
    ' Find the last row of data
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    ' Loop through each row to validate multiple IDs
    For i = 2 To lastRow
        idValue = Trim(ws.Cells(i, ws.Columns(idColumn).Column).value)
        
        ' Split multiple IDs by semicolons
        idList = Split(idValue, ";")
        
        ' Loop through each ID in the list and check its format
        For j = LBound(idList) To UBound(idList)
            If Not regex.Test(Trim(idList(j))) Then
                report = report & "L'identifiant '" & Trim(idList(j)) & "' à la ligne " & i & " dans la colonne '" & idColumn & "' de la feuille '" & sheetName & "' n'est pas dans le bon format." & vbCrLf
                ' Highlight the cell in red
                ws.Cells(i, ws.Columns(idColumn).Column).Interior.Color = RGB(255, 0, 0)
            End If
        Next j
    Next i
End Sub

Sub ValidateNumericValues(ws As Worksheet, columnName As String, sheetName As String, ByRef report As String)
    Dim lastRow As Long, i As Long
    Dim value As String
    
    ' Find the last row of data
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    ' Loop through each row to check if the value is numeric
    For i = 2 To lastRow
        value = ws.Cells(i, ws.Columns(columnName).Column).value
        
        ' Check if the value is not numeric
        If Not IsNumeric(value) Then
            report = report & "La valeur '" & value & "' à la ligne " & i & " dans la colonne '" & columnName & "' de la feuille '" & sheetName & "' n'est pas numérique." & vbCrLf
            ' Highlight the cell in red
            ws.Cells(i, ws.Columns(columnName).Column).Interior.Color = RGB(255, 0, 0)
        End If
    Next i
End Sub

Sub ValidateIDPrefix(ws As Worksheet, idColumn As String, prefix As String, sheetName As String, ByRef report As String)
    Dim lastRow As Long, i As Long
    Dim idValue As String
    
    ' Find the last row of data
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    ' Loop through each row to check the prefix of the ID
    For i = 2 To lastRow
        idValue = ws.Cells(i, ws.Columns(idColumn).Column).value
        
        ' Check if the ID starts with the expected prefix
        If Left(idValue, Len(prefix)) <> prefix Then
            report = report & "L'identifiant '" & idValue & "' à la ligne " & i & " dans la colonne '" & idColumn & "' de la feuille '" & sheetName & "' ne commence pas par le préfixe '" & prefix & "'." & vbCrLf
            ' Highlight the cell in red
            ws.Cells(i, ws.Columns(idColumn).Column).Interior.Color = RGB(255, 0, 0)
        End If
    Next i
End Sub


Sub ValidatePersonneMatches(oeuvreWS As Worksheet, personneWS As Worksheet, oeuvreColumns As Variant, firstNameColumn As String, lastNameColumn As String, sheetNameOeuvre As String, sheetNamePersonne As String, ByRef report As String)
    Dim lastRowOeuvre As Long, lastRowPersonne As Long, i As Long
    Dim personneName As String, personneFirstName As String, combinedName As String
    Dim personneDict As Object
    Set personneDict = CreateObject("Scripting.Dictionary")
    
    ' Load all names from the PERSONNE sheet into a dictionary
    lastRowPersonne = personneWS.Cells(personneWS.Rows.Count, 1).End(xlUp).Row
    For i = 2 To lastRowPersonne
        personneFirstName = Trim(personneWS.Cells(i, ws.Columns(firstNameColumn).Column).value)
        personneName = Trim(personneWS.Cells(i, ws.Columns(lastNameColumn).Column).value)
        combinedName = personneFirstName & " " & personneName
        personneDict.Add combinedName, i
    Next i
    
    ' Find the last row of data in the OEUVRE sheet
    lastRowOeuvre = oeuvreWS.Cells(oeuvreWS.Rows.Count, 1).End(xlUp).Row
    
    ' Loop through each row to check person names
    For i = 2 To lastRowOeuvre
        For Each col In oeuvreColumns
            If Not personneDict.exists(Trim(oeuvreWS.Cells(i, col).value)) Then
                report = report & "Le nom '" & oeuvreWS.Cells(i, col).value & "' à la ligne " & i & " dans la colonne '" & col & "' de la feuille '" & sheetNameOeuvre & "' ne correspond à aucun nom dans la feuille '" & sheetNamePersonne & "'." & vbCrLf
                ' Highlight the cell in red
                oeuvreWS.Cells(i, col).Interior.Color = RGB(255, 0, 0)
            End If
        Next col
    Next i
End Sub

Sub ValidateEditeurImprimeur(sourceWS As Worksheet, editeurColumn As String, personneWS As Worksheet, firstNameColumn As String, lastNameColumn As String, sheetNameSource As String, sheetNamePersonne As String, ByRef report As String)
    Dim lastRowSource As Long, lastRowPersonne As Long, i As Long
    Dim personneName As String, personneFirstName As String, combinedName As String
    Dim personneDict As Object
    Set personneDict = CreateObject("Scripting.Dictionary")
    
    ' Load all names from the PERSONNE sheet into a dictionary
    lastRowPersonne = personneWS.Cells(personneWS.Rows.Count, 1).End(xlUp).Row
    For i = 2 To lastRowPersonne
        personneFirstName = Trim(personneWS.Cells(i, ws.Columns(firstNameColumn).Column).value)
        personneName = Trim(personneWS.Cells(i, ws.Columns(lastNameColumn).Column).value)
        combinedName = personneFirstName & " " & personneName
        personneDict.Add combinedName, i
    Next i
    
    ' Find the last row of data in the SOURCE IMPRIMÉE sheet
    lastRowSource = sourceWS.Cells(sourceWS.Rows.Count, 1).End(xlUp).Row
    
    ' Loop through each row to check Éditeur/Imprimeur names
    For i = 2 To lastRowSource
        If Not personneDict.exists(Trim(sourceWS.Cells(i, ws.Columns(editeurColumn).Column).value)) Then
            report = report & "Le nom de l'Éditeur/Imprimeur '" & sourceWS.Cells(i, ws.Columns(editeurColumn).Column).value & "' à la ligne " & i & " dans la feuille '" & sheetNameSource & "' ne correspond à aucun nom dans la feuille '" & sheetNamePersonne & "'." & vbCrLf
            ' Highlight the cell in red
            sourceWS.Cells(i, ws.Columns(editeurColumn).Column).Interior.Color = RGB(255, 0, 0)
        End If
    Next i
End Sub

Sub ValidateColumns(ws As Worksheet, modelColumns As Variant, sheetName As String, ByRef report As String)
    Dim actualColumns As Variant
    Dim missingColumns As String
    Dim i As Long
    Dim found As Boolean
    
    ' Loop through each model column and check if it exists in the worksheet
    For i = LBound(modelColumns) To UBound(modelColumns)
        found = False
        
        ' Loop through the actual columns in the sheet to see if the model column exists
        actualColumns = ws.Rows(1).value
        For Each col In actualColumns
            If Trim(col) = modelColumns(i) Then
                found = True
                Exit For
            End If
        Next col
        
        ' If the model column is not found, log it as missing
        If Not found Then
            missingColumns = missingColumns & modelColumns(i) & ", "
        End If
    Next i
    
    ' If there are missing columns, log an error
    If missingColumns <> "" Then
        report = report & "Les colonnes manquantes dans la feuille '" & sheetName & "' sont : " & Left(missingColumns, Len(missingColumns) - 2) & "." & vbCrLf
    End If
End Sub

Sub ValidateAndCheckDuplicatesAndIDs(wsModel As Worksheet, wsData As Worksheet, sheetName As String, ByRef report As String)
    Dim modelColumns As Variant
    Dim idColumn As String
    Dim columnNames As Variant
    
    ' Extract column names from the model sheet (this can be adjusted to specific column ranges)
    modelColumns = wsModel.Rows(1).value ' Assuming the model sheet has column names in the first row
    
    ' Validate columns from the data file against the model
    Call ValidateColumns(wsData, modelColumns, sheetName, report)
    
    ' Check for duplicate IDs
    idColumn = "ID" ' Change according to your ID column name
    Call CheckForDuplicates(wsData, Array(idColumn), sheetName, report)
End Sub

Sub ValidateReferencedIDs(wsData As Worksheet, idColumn As String, wsReference As Worksheet, refIDColumn As String, sheetName As String, refSheetName As String, ByRef report As String)
    Dim lastRow As Long, i As Long
    Dim dataID As String
    Dim refIDDict As Object
    Dim refLastRow As Long
    
    Set refIDDict = CreateObject("Scripting.Dictionary")
    
    ' Load reference IDs into a dictionary
    refLastRow = wsReference.Cells(wsReference.Rows.Count, 1).End(xlUp).Row
    For i = 2 To refLastRow
        refIDDict.Add wsReference.Cells(i, wsReference.Columns(refIDColumn).Column).value, i
    Next i
    
    ' Find the last row of data
    lastRow = wsData.Cells(wsData.Rows.Count, 1).End(xlUp).Row
    
    ' Loop through each row to check referenced IDs
    For i = 2 To lastRow
        dataID = wsData.Cells(i, wsData.Columns(idColumn).Column).value
        
        ' Check if the referenced ID exists in the reference sheet
        If Not refIDDict.exists(dataID) Then
            report = report & "L'ID '" & dataID & "' à la ligne " & i & " dans la feuille '" & sheetName & "' ne correspond à aucun ID dans la feuille '" & refSheetName & "'." & vbCrLf
            ' Highlight the cell in red
            wsData.Cells(i, wsData.Columns(idColumn).Column).Interior.Color = RGB(255, 0, 0)
        End If
    Next i
End Sub