fix the URLs incorrectly handled by Excel 2002
Sub fix_url()
    Selection.Hyperlinks(1).Address = Replace(Selection.Hyperlinks(1).Address, "/", "\\")
End Sub

add hyperlinks toward indexed photos
Sub hyperlinkPhoto()
    For i = 1 To ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
        ActiveSheet.Hyperlinks.Add Anchor:=Cells(i, 2), Address:="file://" & ActiveWorkbook.Path & "/" & Cells(i, 1).Text & "/" & Cells(i, 2).Text
    Next i
End Sub

hyperlink the SPRs
Option Explicit
Private Function isSPRIdentifier(txt As String) As Boolean
    isSPRIdentifier = False
    If (Len(txt) <> 10) Then
        Exit Function
    End If
    Dim t As String
    t = Mid(txt, 4, 2)
    If (t <> "ge") Then
        Exit Function
    End If
    Dim i As Integer
    For i = 6 To 10
        t = Mid(txt, i, 1)
        If (t < "0" Or t > "9") Then
            Exit Function
        End If
    Next i
    isSPRIdentifier = True
End Function
Sub hyperlinkDDTS()
    Dim i, j As Integer
    For i = 1 To ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
        For j = 1 To ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column
        Dim txt As String
            txt = Trim(Cells(i, j).Text)
            If (isSPRIdentifier(txt)) Then
                ActiveSheet.Hyperlinks.Add Anchor:=Cells(i, j), Address:="http://frbucdts01medge.euro.med.ge.com/ddts/ddts_main?LastForm=DumpBug&bug_id=" & txt
            End If
        Next j
    Next i
End Sub

hyperlink the requests in JIRA
Option Explicit
Private Function isJIRAIdentifier(txt As String) As Boolean
    isJIRAIdentifier = False
    If ((Mid(txt, 1, 4) <> "VER-") Or (Len(txt) = 4)) Then
        Exit Function
    End If
    Dim i As Integer
    For i = 5 To Len(txt)
        Dim t As String
        t = Mid(txt, i, 1)
        If (t < "0" Or t > "9") Then
            Exit Function
    End If
    Next i
    isJIRAIdentifier = True
End Function
Sub hyperlinkJIRA()
    Dim i, j As Integer
    For i = 1 To ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
        For j = 1 To ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column
            Dim txt As String
            txt = Trim(Cells(i, j).Text)
            If (isJIRAIdentifier(txt)) Then
                ActiveSheet.Hyperlinks.Add Anchor:=Cells(i, j), Address:="https://jira.kikamedical.net/browse/" & txt
            End If
        Next j
    Next i
End Sub

compare two sheets in two different files
Option Explicit

Sub compare()

    Dim file(2), shet(2) As String
    file(0) = "bug4.csv" ' name of first file
    file(1) = "bug5.csv" ' name of second file
    shet(0) = "bug4" ' name of sheet in first file
    shet(1) = "bug5" ' name of sheet in second file

    Dim sht(2) As Excel.Worksheet
    Windows(file(0)).Activate
    Set sht(0) = Application.Sheets(shet(0))
    sht(0).Select
    ActiveSheet.UsedRange
    Windows(file(1)).Activate
    Set sht(1) = Application.Sheets(shet(1))
    sht(1).Select
    ActiveSheet.UsedRange

    If sht(0).Cells.SpecialCells(xlCellTypeLastCell).Row <> sht(1).Cells.SpecialCells(xlCellTypeLastCell).Row Then
        MsgBox "the two sheets do not have the same number of rows"
        Exit Sub
    End If

    If sht(0).Cells.SpecialCells(xlCellTypeLastCell).Column <> sht(1).Cells.SpecialCells(xlCellTypeLastCell).Column Then
        MsgBox "the two sheets do not have the same number of columns"
        Exit Sub
    End If

    Dim i, j, k As Integer
    k = 0
    For i = 1 To sht(0).Cells.SpecialCells(xlCellTypeLastCell).Row
        For j = 1 To sht(0).Cells.SpecialCells(xlCellTypeLastCell).Column
            If sht(0).Cells(i, j).Value <> sht(1).Cells(i, j).Value Then
                k = 1
                sht(0).Cells(i, j).Interior.ColorIndex = 6
                sht(1).Cells(i, j).Interior.ColorIndex = 6
            End If
        Next j
    Next i

    If k = 1 Then
        MsgBox "there are some differences"
    End If
End Sub

merge two sheets according to a key present on both of them
Option Explicit

Private Function ConvertColumnNumberToLetter(ByVal ColumnNumber As Integer)
    Dim IntegerResult As Integer
    Dim FractionalResult As Integer
    Dim Remainder As Integer
    Dim FirstLetter As String
    Dim SecondLetter As String
    IntegerResult = ColumnNumber \ 26
    FractionalResult = (ColumnNumber / 26) - IntegerResult
    Remainder = ColumnNumber Mod 26
    If IntegerResult = 0 Then
        FirstLetter = ""
    ElseIf IntegerResult = 1 And FractionalResult = 0 Then
        FirstLetter = ""
        ConvertColumnNumberToLetter = "Z"
        Exit Function
    ElseIf IntegerResult > 1 And FractionalResult = 0 Then
        FirstLetter = Chr(64 + (IntegerResult - 1))
        ConvertColumnNumberToLetter = FirstLetter & "Z"
        Exit Function
    Else
        FirstLetter = Chr(64 + IntegerResult)
    End If
    SecondLetter = Chr(64 + Remainder)
    ConvertColumnNumberToLetter = FirstLetter & SecondLetter
End Function

Sub merge()
    Dim source(2) As Variant
    Dim keyColumns(2) As String
    Set source(0) = Sheets("Sheet2") ' name of the SRS sheet
    Set source(1) = Sheets("Sheet1") ' name of the VP sheet
    keyColumns(0) = "A"            ' column containing the Requirement ID in the SRS sheet
    keyColumns(1) = "B"            ' column containing the Requirement ID in the VP sheet
    
    
    ' -------------------------------------
    
    Dim i As Integer
    Application.ScreenUpdating = False
    Dim newSheet As Variant
    Dim numberOfRows(2) As Integer, numberOfColumns(2) As Integer
    For i = 0 To 1
        Dim x As Long
        x = source(i).UsedRange.Rows.Count 'Attempt to fix the lastcell on the worksheet
        numberOfRows(i) = source(i).Cells.SpecialCells(xlLastCell).Row
        numberOfColumns(i) = source(i).Cells.SpecialCells(xlLastCell).Column
    Next i
    
    ' copy first sheet and sort it
    source(0).Cells.copy
    Set newSheet = Worksheets.Add
    newSheet.Paste
    newSheet.Cells.Sort Key1:=Range(keyColumns(0) & "2"), Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    
    ' copy titles
    source(1).Range("A1:" & ConvertColumnNumberToLetter(numberOfColumns(1)) & "1").copy
    Range(ConvertColumnNumberToLetter(numberOfColumns(0) + 1) & "1").Select
    newSheet.Paste
    
    For i = 2 To numberOfRows(1)
        Dim key(2) As Variant
        key(1) = source(1).Range(keyColumns(1) & i).Text
        Dim j As Integer
        Dim found As Boolean
        j = 2
        found = False
        Do While ((j <= numberOfRows(0)) And (Not found))
            key(0) = Range(keyColumns(0) & j).Text
            If (key(1) = key(0)) Then
                found = True
            Else
                j = j + 1
            End If
        Loop
        If (found) Then
            If (newSheet.Range(ConvertColumnNumberToLetter(numberOfColumns(0) + 1) & j).Text = "") Then
                ' we add data on the matching line
                newSheet.Range(ConvertColumnNumberToLetter(numberOfColumns(0) + 1) & j).Select
            Else
                ' the machine line already contains data, we insert a new line
                Application.CutCopyMode = False
                newSheet.Rows((j + 1) & ":" & (j + 1)).Insert Shift:=xlDown
                Dim k As Integer
                For k = 1 To numberOfColumns(0)
                    Dim l As String
                    l = ConvertColumnNumberToLetter(k)
                    If (l = keyColumns(0)) Then
                        Range(l & j & ":" & l & (j + 1)).MergeCells = True
                    Else
                        Range(l & (j + 1) & ":" & l & (j + 1)).Value = Range(l & j & ":" & l & j).Value
                    End If
                Next k
                numberOfRows(0) = numberOfRows(0) + 1
                newSheet.Range(ConvertColumnNumberToLetter(numberOfColumns(0) + 1) & (j + 1)).Select
            End If
        Else
            ' there is no matching line, we insert at the end of the sheet
            numberOfRows(0) = numberOfRows(0) + 1
            newSheet.Range(ConvertColumnNumberToLetter(numberOfColumns(0) + 1) & numberOfRows(0)).Select
        End If
        source(1).Range("A" & i & ":" & ConvertColumnNumberToLetter(numberOfColumns(1)) & i).copy
        newSheet.Paste
    Next i

    newSheet.Columns.WrapText = False
    newSheet.Columns.AutoFit
    Rows("1:1").HorizontalAlignment = xlCenter
    Rows("1:1").Font.Bold = True

    Application.ScreenUpdating = True
End Sub

create a graph of frequencies from an Excel database
Option Explicit

Sub CreateRootCauseGraph()

    Dim source As Variant
    Dim newSheet As Variant
    Dim reportNumberColumn As String
    Dim rootCauseColumn As String
    Dim closureCodeColumn As String
    Dim statusColumn As String
    
    Set source = Sheets("PQR_list") ' name of the sheet containing the PQR list
    reportNumberColumn = "A ' column containing the report number
    rootCauseColumn = "E" ' column containing the Root Cause / analyze
    closureCodeColumn = "H" ' column containing the Closure Code
    statusColumn = "D" ' column containing the Status
    
    Dim totalNumber As Integer
    Dim incrNumber As Integer
    totalNumber = source.UsedRange.Rows.Count
    incrNumber = 1
    
    Application.ScreenUpdating = False
    
    ' --- --- --- create new sheet to record root cause of each PQR ---
    Set newSheet = Worksheets.Add
    newSheet.Name = "Root Cause Analysis"
    newSheet.Cells(incrNumber, 1) = "identifier"
    newSheet.Cells(incrNumber, 2) = "root cause"
    
    ' --- --- --- extract root cause of each PQR ---
    Dim i As Integer
    Dim text As String
    Dim pos As Integer
    For i = 1 To totalNumber
        If (source.Cells(i, closureCodeColumn) = "CA taken" And _
                (source.Cells(i, statusColumn) = "Resolved" Or _
                source.Cells(i, statusColumn) = "Verified" Or _
                source.Cells(i, statusColumn) = "Closed")) Then
            incrNumber = incrNumber + 1
            text = source.Cells(i, rootCauseColumn)
            pos = InStr(text, "]")
            If ((Left(text, 1) = "[") And (pos &gt; 1)) Then
                text = Mid(text, 2, pos - 2)
            Else
                text = "?"
            End If
            newSheet.Cells(incrNumber, 1) = source.Cells(i, reportNumberColumn)
            newSheet.Cells(incrNumber, 2) = text
        End If
    Next i
    
    ' --- --- --- create pivot table ---
    Dim table As Variant
    Set table = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:="'Root Cause Analysis'!A:B").CreatePivotTable(Range("D1"))
    table.AddFields RowFields:="root cause"
    table.PivotFields("identifier").Orientation = xlDataField
    table.DataFields(1).Function = xlCount
    
    Dim numberOfValues As Integer
    numberOfValues = table.DataBodyRange.Rows.Count

    ' --- --- --- create chart ---
    Dim chart As Variant
    Set chart = Charts.Add
    chart.ChartType = xlColumnClustered
    chart.SetSourceData source:=Sheets("Root Cause Analysis").Range("A1:B" & incrNumber), PlotBy:=xlRows
    chart.SeriesCollection(1).XValues = "='Root Cause Analysis'!R3C4:R" & numberOfValues & "C4"
    chart.SeriesCollection(1).Values = "='Root Cause Analysis'!R3C5:R" & numberOfValues & "C5"
    chart.HasLegend = False
    chart.HasDataTable = False
    chart.HasTitle = False
    chart.Location Where:=xlLocationAsObject, Name:="Root Cause Analysis"

    Application.ScreenUpdating = True
    
End Sub