version6

version6

Sub CompareASILLevels()
    Dim ws As Worksheet, reportWs As Worksheet

    ' Ensure the specific input sheet exists
    On Error Resume Next
    Set ws = Worksheets("ASIL_Data")
    On Error GoTo 0

    If ws Is Nothing Then
        MsgBox "Error: 'ASIL_Data' sheet not found.", vbExclamation
        Exit Sub
    End If

    ' Check if the report sheet exists, create or clear it
    On Error Resume Next
    Set reportWs = Worksheets("ASIL_Report")
    On Error GoTo 0

    If reportWs Is Nothing Then
        Set reportWs = Worksheets.Add
        reportWs.Name = "ASIL_Report"
    Else
        reportWs.Cells.Clear
    End If

    ' Set report headers
    reportWs.Cells(1, 1).Value = "ID"
    reportWs.Cells(1, 2).Value = "Receiver Arch Element"
    reportWs.Cells(1, 3).Value = "SubElement"
    reportWs.Cells(1, 4).Value = "Receiver ASIL"
    reportWs.Cells(1, 5).Value = "Sender ASIL"
    reportWs.Cells(1, 6).Value = "Sender Arch Element"
    reportWs.Cells(1, 7).Value = "Status"

    ' ASIL priority mapping
    Dim asilPriority As Object
    Set asilPriority = CreateObject("Scripting.Dictionary")
    asilPriority("ASIL D") = 5
    asilPriority("ASIL C") = 4
    asilPriority("ASIL B") = 3
    asilPriority("ASIL A") = 2
    asilPriority("QM") = 1
    asilPriority("-") = 0

    Dim i As Long, j As Long, reportRow As Long
    reportRow = 2

    ' Loop through Receivers
    For i = 2 To ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
        If Trim(ws.Cells(i, 5).Value) = "Reciever" Then

            Dim receiverASIL As String, receiverSubElement As String
            receiverASIL = Trim(ws.Cells(i, 4).Value)
            receiverSubElement = Trim(ws.Cells(i, 3).Value)

            Dim senderFound As Boolean: senderFound = False

            ' Match with all Senders
            For j = 2 To ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
                If Trim(ws.Cells(j, 5).Value) = "Sender" And Trim(ws.Cells(j, 3).Value) = receiverSubElement Then
                    senderFound = True

                    Dim senderASIL As String, senderArchElement As String
                    senderASIL = Trim(ws.Cells(j, 4).Value)
                    senderArchElement = Trim(ws.Cells(j, 2).Value)

                    ' Populate report
                    reportWs.Cells(reportRow, 1).Value = ws.Cells(i, 1).Value
                    reportWs.Cells(reportRow, 2).Value = ws.Cells(i, 2).Value
                    reportWs.Cells(reportRow, 3).Value = receiverSubElement
                    reportWs.Cells(reportRow, 4).Value = receiverASIL
                    reportWs.Cells(reportRow, 5).Value = senderASIL
                    reportWs.Cells(reportRow, 6).Value = senderArchElement

                    ' ASIL Check
                    If IsEmpty(senderASIL) Or IsEmpty(receiverASIL) Then
                        reportWs.Cells(reportRow, 7).Value = "Error: Missing ASIL Level"
                    ElseIf senderASIL = "-" Or receiverASIL = "-" Then
                        reportWs.Cells(reportRow, 7).Value = "Error: Missing ASIL Level"
                    ElseIf asilPriority(senderASIL) < asilPriority(receiverASIL) Then
                        reportWs.Cells(reportRow, 7).Value = "Invalid: Sender ASIL is lower"
                    Else
                        reportWs.Cells(reportRow, 7).Value = "Valid"
                    End If
                    reportRow = reportRow + 1
                End If
            Next j

            ' Handle missing Sender
            If Not senderFound Then
                reportWs.Cells(reportRow, 1).Value = ws.Cells(i, 1).Value
                reportWs.Cells(reportRow, 2).Value = ws.Cells(i, 2).Value
                reportWs.Cells(reportRow, 3).Value = receiverSubElement
                reportWs.Cells(reportRow, 4).Value = receiverASIL
                reportWs.Cells(reportRow, 5).Value = "Not Found"
                reportWs.Cells(reportRow, 6).Value = "Not Found"
                reportWs.Cells(reportRow, 7).Value = "Error: No matching Sender"
                reportRow = reportRow + 1
            End If
        End If
    Next i

    ' Auto-arrange the report
    With reportWs
        .Columns("A:G").AutoFit
        .Rows("1:1").Font.Bold = True
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:=.Range("B:B"), Order:=xlAscending
        .Sort.SortFields.Add Key:=.Range("C:C"), Order:=xlAscending
        .Sort.SetRange .Range("A:G")
        .Sort.Header = xlYes
        .Sort.Apply
    End With

    MsgBox "ASIL Level Comparison and Report Generation Completed", vbInformation
End Sub