Bonjour,

J'ai défini des variables et je souhaite prendre la 2 ème plus petite valeurs de ces dernières.
Je n'arrive pas à adapter la fonction Small dans mon code VBA.

J'ai indiqué en rouge la function que je veux adapter.

Si quelqu'un a une solution...

Merci beaucoup!

Kesdo

Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
Dim SPRatings As Variant, FitchRatings As Variant, DBRSRatings As Variant, MoodysRatings As Variant, ERRatings As Variant, Ratings As Variant

    SPRatings = Array("AAA", "AA+", "AA", "AA-", "A+", "A", "A-", "BBB+", "BBB", "BBB-", "BB+", "BB", "BB-", "B+", "B", "B-", "CCC+", "CCC", "CCC-", "NR")
    FitchRatings = Array("AAA", "AA+", "AA", "AA-", "A+", "A", "A-", "BBB+", "BBB", "BBB-", "BB+", "BB", "BB-", "B+", "B", "B-", "CCC+", "CCC", "CCC-", "NR")
    DBRSRatings = Array("AAA", "AA(High)", "AA", "AA(Low)", "A(High)", "A", "A(Low)", "BBB(High)", "BBB", "BBB(Low)", "BB(High)", "BB", "BB(Low)", "B(High)", "B", "B(Low)", "CCC(High)", "CCC", "CCC(Low)", "NR")
    MoodysRatings = Array("Aaa", "Aa1", "Aa2", "Aa3", "A1", "A2", "A3", "Baa1", "Baa2", "Baa3", "Ba1", "Ba2", "Ba3", "B1", "B2", "B3", "Caa1", "Caa2", "Caa3", "NR")
   
    Ratings = Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "0")
    
    ub = UBound(Ratings)
    lb = LBound(Ratings)


    For j = lb To ub
         If ERRating = CStr(MoodysRatings(j)) Then
         ERRating = Ratings(j)
         End If
         If MoodysRating = CStr(MoodysRatings(j)) Then
         MoodysRating = Ratings(j)
         End If
         If FitchRating = CStr(FitchRatings(j)) Then
         FitchRating = Ratings(j)
         End If
         If DBRSRating = CStr(DBRSRatings(j)) Then
         DBRSRating = Ratings(j)
         End If
         If SPRating = CStr(SPRatings(j)) Then
         SPRating = Ratings(j)
         End If
    Next j

RetainRating = WorksheetFunction.Small({ERRating, MoodysRating, FitchRating, DBRSRating, SPRating};2)