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 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68
|
Public Sub frequence()
Dim freq() As Integer
Dim icol As Integer, iLigne As Integer
Dim i As Integer
icol = 1
iLigne = 6
ReDim freq(0)
Do While Range("A" & iLigne).Value <> ""
icol = 1
Do While Cells(iLigne, icol).Value <> ""
'Regarde si la valeur rentre dans le tableau
If UBound(freq) < Cells(iLigne, icol).Value Then
ReDim Preserve freq(Cells(iLigne, icol).Value)
End If
freq(Cells(iLigne, icol).Value) = freq(Cells(iLigne, icol).Value) + 1
icol = icol + 1
Loop
iLigne = iLigne + 1
Loop
'Insere les infos ligne 18
icol = 8
iLigne = 22
For i = 0 To UBound(freq)
If freq(i) > 0 Then
'Infos num avec frequence
Cells(18, icol).Value = i & "(" & freq(i) & ")"
icol = icol + 1
Cells(iLigne, 3).Value = i
Cells(iLigne, 4).Value = freq(i)
iLigne = iLigne + 1
End If
Next i
'Tri les infos
Cells(22, 4).Select
Selection.CurrentRegion.Select
Selection.Sort Key1:=Range("D22"), Order1:=xlDescending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
'Copie les infos
Range("C22:C" & iLigne).Select
Selection.Copy
Range("H20").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Cells(22, 4).Select
Selection.CurrentRegion.Select
Selection.Clear
Range("A1").Select
End Sub |
Partager