Sub total_closed()
'
' total_closed Macro
'
Dim maxligne As Integer
Dim tab_p(75) As String
Dim tab_l(15) As String
Dim tab_c(27) As String
Dim status As String

'remplissage du tableau tab_p
tab_p(0) = "BBANG"
tab_p(1) = "BDANG"
tab_p(2) = "BEANG"
tab_p(3) = "BKANG"
tab_p(4) = "BPANG"
tab_p(5) = "BYANG"
tab_p(6) = "BZANG"
tab_p(7) = "CBANG"
tab_p(8) = "CHANG"
tab_p(9) = "DDANG"
tab_p(10) = "DMANG"
tab_p(11) = "DSANG"
tab_p(12) = "EAANG"
tab_p(13) = "EBANG"
tab_p(14) = "EJANG"
tab_p(15) = "F0ANG"
tab_p(16) = "F1ANG"
tab_p(17) = "F2ANG"
tab_p(18) = "F3ANG"
tab_p(19) = "FAANG"
tab_p(20) = "FNPANG"
tab_p(21) = "QIANG"
tab_p(22) = "T3ANG"
tab_p(23) = "TAANG"
tab_p(24) = "TBANG"
tab_p(25) = "UTANG"
tab_p(26) = "DFRANG"
tab_p(27) = "QFANG"
tab_p(28) = "TPEND"
tab_p(29) = "FPP"
tab_p(30) = "H4ANG"
tab_p(31) = "H4PP"
tab_p(32) = "OTANG"
tab_p(33) = "BF"
tab_p(34) = "FC"
tab_p(35) = "BC"
tab_p(36) = "FCANG"
tab_p(37) = "BV"
tab_p(38) = "B1"
tab_p(39) = "FV"
tab_p(40) = "C1"
tab_p(41) = "EM"
tab_p(42) = "ED"
tab_p(43) = "FP"
tab_p(44) = "FLANG"
tab_p(45) = "FL"
tab_p(46) = "UU"
tab_p(47) = "HA"
tab_p(48) = "HB"
tab_p(49) = "HC"
tab_p(50) = "HE"
tab_p(51) = "HF"
tab_p(52) = "LCANG"
tab_p(53) = "JE"
tab_p(54) = "HAANG"
tab_p(55) = "JD"
tab_p(56) = "OS"
tab_p(57) = "OD"
tab_p(58) = "OT"
tab_p(59) = "OSANG"
tab_p(60) = "UW"
tab_p(61) = "QI"
tab_p(62) = "QM"
tab_p(63) = "QMANG"
tab_p(64) = "QPANG"
tab_p(65) = "QU"
tab_p(66) = "QUANG"
tab_p(67) = "T0"
tab_p(68) = "FV"
tab_p(69) = "CT"
tab_p(70) = "CTANG"
tab_p(71) = "DA"
tab_p(72) = "FA"
tab_p(73) = "OV"
tab_p(74) = "TE"
tab_p(75) = "TB"


'remplissage du tableau tab_l
tab_l(0) = "HSANG"
tab_l(1) = "J4ANG"
tab_l(2) = "J5ANG"
tab_l(3) = "J6ANG"
tab_l(4) = "JAANG"
tab_l(5) = "JBANG"
tab_l(6) = "JCANG"
tab_l(7) = "JCOANG"
tab_l(8) = "JFANG"
tab_l(9) = "LCOANG"
tab_l(10) = "LCANG"
tab_l(11) = "HAANG"
tab_l(12) = "I6ANG"
tab_l(13) = "IKANG"
tab_l(14) = "IG4"
tab_l(15) = "IEANG"


'remplissage du tableau tab_c
tab_c(0) = "I3ANG"
tab_c(1) = "I5ANG"
tab_c(2) = "IAANG"
tab_c(3) = "IDANG"
tab_c(4) = "IDEANG"
tab_c(5) = "IEFANG"
tab_c(6) = "IFANG"
tab_c(7) = "IGANG"
tab_c(8) = "IHANG"
tab_c(9) = "ILANG"
tab_c(10) = "IOANG"
tab_c(11) = "ISANG"
tab_c(12) = "SCANG"
tab_c(13) = "SDANG"
tab_c(14) = "SJANG"
tab_c(15) = "SOANG"
tab_c(16) = "SAANG"
tab_c(17) = "IGH4"
tab_c(18) = "IGREA"
tab_c(19) = "IMANG"
tab_c(20) = "INANG"
tab_c(21) = "I6ANG"
tab_c(22) = "IKANG"
tab_c(23) = "IG4"
tab_c(24) = "SAREA"
tab_c(25) = "SCREA"
tab_c(26) = "SCH4"
tab_c(27) = "SAH4"


'récupère le nombre de ligne de la plage de données
maxligne = ActiveSheet.UsedRange.Rows.Count

Range("E" & maxligne + 1) = maxligne - 5
'on fait la somme des montants
total_principal = 0
total_solde = 0

For i = 6 To maxligne
    code_position = Range("K" & i).Value

    'récupère le status
    If IsError(Application.Match(code_position, tab_p, 0)) Then
    'valeur non trouvée
        If IsError(Application.Match(code_position, tab_l, 0)) Then
        'valeur non trouvée
            If IsError(Application.Match(code_position, tab_c, 0)) Then
                If code_position = "M" Then
                    status = "M"
                End If
            'valeur non trouvée
            Else
            'valeur trouvée
                status = "C"
            End If
        Else
        'valeur trouvée
            status = "L"
        End If
    Else
    'valeur trouvée
        status = "P"
    End If
    Range("J" & i) = status
    Range("K" & i) = correspondance_status(code_position)
    total_principal = total_principal + Range("G" & i)
    total_recovered = total_recovered + Range("H" & i)
    total_solde = total_solde + Range("I" & i)
    Range("A" & i & ":L" & i).Select
    Selection.Borders.Weight = xlThin
    Selection.HorizontalAlignment = xlCenter
    
Next i

Range("A" & maxligne + 1) = "Total"
Range("G" & maxligne + 1) = total_principal
Range("H" & maxligne + 1) = total_recovered
Range("I" & maxligne + 1) = total_solde
Range("A" & i & ":L" & i).Select
Selection.Borders.Weight = xlThin
Selection.Interior.Color = RGB(178, 178, 178)
Selection.Font.Bold = True
Selection.HorizontalAlignment = xlCenter

End Sub

Function correspondance_status(status)

Dim tab_s(131, 2) As String

'tableau de correspondance
tab_s(0, 0) = "BBANG"
tab_s(0, 1) = "RM1"
'----
tab_s(1, 0) = "BDANG"
tab_s(1, 1) = "RM3"
'----
tab_s(2, 0) = "BF"
tab_s(2, 1) = "RM3"
'----
tab_s(3, 0) = "FC"
tab_s(3, 1) = "RM3"
'----
tab_s(4, 0) = "BC"
tab_s(4, 1) = "RM3"
'----
tab_s(5, 0) = "FCANG"
tab_s(5, 1) = "RM3"
'----
tab_s(6, 0) = "BEANG"
tab_s(6, 1) = "RML"
'----
tab_s(7, 0) = "B1"
tab_s(7, 1) = "RML"
'----
tab_s(8, 0) = "BV"
tab_s(8, 1) = "RML"
'----
tab_s(9, 0) = "BKANG"
tab_s(9, 1) = "RM1"
'----
tab_s(10, 0) = "BPANG"
tab_s(10, 1) = "PRI"
'----
tab_s(11, 0) = "BYANG"
tab_s(11, 1) = "PEN"
'----
tab_s(12, 0) = "FV"
tab_s(12, 1) = "PEN"
'----
tab_s(13, 0) = "BZANG"
tab_s(13, 1) = "PEN"
'----
tab_s(14, 0) = "CBANG"
tab_s(14, 1) = "PEN"
'----
tab_s(15, 0) = "C1"
tab_s(15, 1) = "PEN"
'----
tab_s(16, 0) = "CHANG"
tab_s(16, 1) = "RST"
'----
tab_s(17, 0) = "DDANG"
tab_s(17, 1) = "PEN"
'----
tab_s(18, 0) = "DFRANG"
tab_s(18, 1) = "FRA"
'----
tab_s(19, 0) = "DMANG"
tab_s(19, 1) = "SMIN"
'----
tab_s(20, 0) = "DSANG"
tab_s(20, 1) = "CAR"
'----
tab_s(21, 0) = "EAANG"
tab_s(21, 1) = "UNR"
'----
tab_s(22, 0) = "EM"
tab_s(22, 1) = "UNR"
'----
tab_s(23, 0) = "EBANG"
tab_s(23, 1) = "ADR"
'----
tab_s(24, 0) = "ED"
tab_s(24, 1) = "ADR"
'----
tab_s(25, 0) = "EJANG"
tab_s(25, 1) = "REE"
'----
tab_s(26, 0) = "F0ANG"
tab_s(26, 1) = "PPP"
'----
tab_s(27, 0) = "F1ANG"
tab_s(27, 1) = "PPP"
'----
tab_s(28, 0) = "F2ANG"
tab_s(28, 1) = "RM3"
'----
tab_s(29, 0) = "FP"
tab_s(29, 1) = "RM3"
'----
tab_s(30, 0) = "F3ANG"
tab_s(30, 1) = "RM3"
'----
tab_s(31, 0) = "FAANG"
tab_s(31, 1) = "SERP"
'----
tab_s(32, 0) = "FLANG"
tab_s(32, 1) = "SERP"
'----
tab_s(33, 0) = "FL"
tab_s(33, 1) = "SERP"
'----
tab_s(34, 0) = "FNPANG"
tab_s(34, 1) = "PPP"
'----
tab_s(35, 0) = "FPP"
tab_s(35, 1) = "REPP"
'----
tab_s(36, 0) = "H4ANG"
tab_s(36, 1) = "BAPR"
'----
tab_s(37, 0) = "H4PP"
tab_s(37, 1) = "BAPP"
'----
tab_s(38, 0) = "HSANG"
tab_s(38, 1) = "coe3"
'----
tab_s(39, 0) = "UU"
tab_s(39, 1) = "COE"
'----
tab_s(40, 0) = "HA"
tab_s(40, 1) = "COE"
'----
tab_s(41, 0) = "HB"
tab_s(41, 1) = "COE"
'----
tab_s(42, 0) = "HC"
tab_s(42, 1) = "COE"
'----
tab_s(43, 0) = "HD"
tab_s(43, 1) = "COE"
'----
tab_s(44, 0) = "HE"
tab_s(44, 1) = "COE"
'----
tab_s(45, 0) = "HF"
tab_s(45, 1) = "COE"
'----
tab_s(46, 0) = "I3ANG"
tab_s(46, 1) = "SCRL"
'----
tab_s(47, 0) = "I5ANG"
tab_s(47, 1) = "SCON"
'----
tab_s(48, 0) = "IAANG"
tab_s(48, 1) = "SCLR"
'----
tab_s(49, 0) = "I6ANG"
tab_s(49, 1) = "SCLR"
'----
tab_s(50, 0) = "IKANG"
tab_s(50, 1) = "SCLR"
'----
tab_s(51, 0) = "IDANG"
tab_s(51, 1) = "SUNR"
'----
tab_s(52, 0) = "IDEANG"
tab_s(52, 1) = "SUNA"
'----
tab_s(53, 0) = "IEFANG"
tab_s(53, 1) = "FRA"
'----
tab_s(54, 0) = "IFANG"
tab_s(54, 1) = "STIB"
'----
tab_s(55, 0) = "IGANG"
tab_s(55, 1) = "REE"
'----
tab_s(56, 0) = "IGH4"
tab_s(56, 1) = "SBUN"
'----
tab_s(57, 0) = "IGREA"
tab_s(57, 1) = "SRUN"
'----
tab_s(58, 0) = "IHANG"
tab_s(58, 1) = "INS"
'----
tab_s(59, 0) = "IG4"
tab_s(59, 1) = "INS"
'----
tab_s(60, 0) = "ILANG"
tab_s(60, 1) = "SUNE"
'----
tab_s(61, 0) = "IMANG"
tab_s(61, 1) = "SODN"
'----
tab_s(62, 0) = "INANG"
tab_s(62, 1) = "SODN"
'----
tab_s(63, 0) = "IOANG"
tab_s(63, 1) = "SPRO"
'----
tab_s(64, 0) = "ISANG"
tab_s(64, 1) = "NST"
'----
tab_s(65, 0) = "J4ANG"
tab_s(65, 1) = "COO"
'----
tab_s(66, 0) = "J5ANG"
tab_s(66, 1) = "COO"
'-----
tab_s(67, 0) = "J6ANG"
tab_s(67, 1) = "PAB"
'----
tab_s(68, 0) = "JAANG"
tab_s(68, 1) = "COO"
'----
tab_s(69, 0) = "LCANG"
tab_s(69, 1) = "COO"
'----
tab_s(70, 0) = "JBANG"
tab_s(70, 1) = "COO"
'----
tab_s(71, 0) = "JE"
tab_s(71, 1) = "COO"
'----
tab_s(72, 0) = "JCANG"
tab_s(72, 1) = "COO"
'----
tab_s(73, 0) = "HAANG"
tab_s(73, 1) = "COO"
'----
tab_s(74, 0) = "JCOANG"
tab_s(74, 1) = "COP"
'----
tab_s(75, 0) = "JD"
tab_s(75, 1) = "COP"
'----
tab_s(76, 0) = "JFANG"
tab_s(76, 1) = "ENO"
'----
tab_s(77, 0) = "LCOANG"
tab_s(77, 1) = "COO"
'----
tab_s(78, 0) = "M"
tab_s(78, 1) = "MNT"
'----
tab_s(79, 0) = "OTANG"
tab_s(79, 1) = "DEC"
'----
tab_s(80, 0) = "OS"
tab_s(80, 1) = "DEC"
'----
tab_s(81, 0) = "OD"
tab_s(81, 1) = "DEC"
'----
tab_s(82, 0) = "OT"
tab_s(82, 1) = "DEC"
'----
tab_s(83, 0) = "OSANG"
tab_s(83, 1) = "DEC"
'----
tab_s(84, 0) = "UW"
tab_s(84, 1) = "DEC"
'----
tab_s(85, 0) = "QFANG"
tab_s(85, 1) = "FRA"
'----
tab_s(86, 0) = "QIANG"
tab_s(86, 1) = "ODN"
'----
tab_s(87, 0) = "SAANG"
tab_s(87, 1) = "SERP"
'----
tab_s(88, 0) = "SAH4"
tab_s(88, 1) = "SBAP"
'----
tab_s(89, 0) = "SAREA"
tab_s(89, 1) = "SREP"
'----
tab_s(90, 0) = "SCANG"
tab_s(90, 1) = "SPPS"
'----
tab_s(91, 0) = "SCH4"
tab_s(91, 1) = "BACP"
'----
tab_s(92, 0) = "SCREA"
tab_s(92, 1) = "RECP"
'----
tab_s(93, 0) = "SDANG"
tab_s(93, 1) = "SERP"
'----
tab_s(94, 0) = "SJANG"
tab_s(94, 1) = "SERL"
'----
tab_s(95, 0) = "SOANG"
tab_s(95, 1) = "SCOM"
'----
tab_s(96, 0) = "T3ANG"
tab_s(96, 1) = "RM3"
'----
tab_s(97, 0) = "TAANG"
tab_s(97, 1) = "PHO"
'----
tab_s(98, 0) = "TO"
tab_s(98, 1) = "PHO"
'----
tab_s(99, 0) = "TBANG"
tab_s(99, 1) = "PEN"
'----
tab_s(100, 0) = "FV"
tab_s(100, 1) = "PEN"
'----
tab_s(101, 0) = "TPEND"
tab_s(101, 1) = "REPR"
'----
tab_s(102, 0) = "UTANG"
tab_s(102, 1) = "COM"
'----
tab_s(103, 0) = "BB"
tab_s(103, 1) = "RM1"
'----
tab_s(104, 0) = "BD"
tab_s(104, 1) = "RM3"
'----
tab_s(105, 0) = "BE"
tab_s(105, 1) = "RML"
'----
tab_s(106, 0) = "E"
tab_s(106, 1) = "RM1"
'----
tab_s(107, 0) = "F0"
tab_s(107, 1) = "PPP"
'----
tab_s(108, 0) = "F3"
tab_s(108, 1) = "RM3"
'----
tab_s(109, 0) = "I5"
tab_s(109, 1) = "SCON"
'----
tab_s(110, 0) = "IL"
tab_s(110, 1) = "SUNE"
'----
tab_s(111, 0) = "IN"
tab_s(111, 1) = "SODN"
'----
tab_s(112, 0) = "IO"
tab_s(112, 1) = "SPRO"
'----
tab_s(113, 0) = "JB"
tab_s(113, 1) = "COO"
'----
tab_s(114, 0) = "JC"
tab_s(114, 1) = "COO"
'----
tab_s(115, 0) = "QI"
tab_s(115, 1) = "ODN"
'----
tab_s(116, 0) = "QM"
tab_s(116, 1) = "ODN"
'----
tab_s(117, 0) = "QMANG"
tab_s(117, 1) = "ODN"
'----
tab_s(118, 0) = "QPANG"
tab_s(118, 1) = "ODN"
'-----
tab_s(119, 0) = "QU"
tab_s(119, 1) = "ODN"
'----
tab_s(120, 0) = "QUANG"
tab_s(120, 1) = "ODN"
'----
tab_s(121, 0) = "SA"
tab_s(121, 1) = "SERP"
'----
tab_s(122, 0) = "T0"
tab_s(122, 1) = "PHO"
'----
tab_s(123, 0) = "TA"
tab_s(123, 1) = "PHO"
'----
tab_s(124, 0) = "TB"
tab_s(124, 1) = "PEN"
'----
tab_s(124, 0) = "CT"
tab_s(124, 1) = "CBANG"
'----
tab_s(125, 0) = "CTANG"
tab_s(125, 1) = "CBANG"
'----
tab_s(126, 0) = "DA"
tab_s(126, 1) = "BYANG"
'----
tab_s(127, 0) = "FA"
tab_s(127, 1) = "SERP"
'----
tab_s(128, 0) = "FI"
tab_s(128, 1) = "F3ANG"
'----
tab_s(129, 0) = "IEANG"
tab_s(129, 1) = "IAANG"
'----
tab_s(130, 0) = "OV"
tab_s(130, 1) = "BYANG"
'----
tab_s(131, 0) = "TE"
tab_s(131, 1) = "BKANG"


trouve = False
i = 0
correspondance_status = ""

Do While (trouve = False) And (i < UBound(tab_s))
    If tab_s(i, 0) = status Then
        trouve = True
    Else
        i = i + 1
    End If
Loop

If trouve = True Then
    correspondance_status = tab_s(i, 1)
End If
End Function