LE TRI DECROISSANT

Rhooolala, mais qu'est ce que c'est compliqué ! C'est plein d'indices qui se baladent.
J'ai mal au crâne, rien que d'essayer de comprendre.

Bon, regarde un peu ça : c'est quand même plus simple :

Const tabsize = 100
' // Ca c'est ton code.
Dim l As Long
l = Worksheets("Feuil1").Cells(Rows.Count, 1).End(xlUp).Row
 
' // Là, c'est une vérif, on ne sait jamais !
If l < tabsize Then
    MsgBox "Alerte !!!!!!! Faut faire quelque chose.", vbCritical
    Exit Sub
End If
 
' // Ca c'est le mien, pas trop compliqué
' // On charge le tableau
Dim table(1 To tabsize) As Double
Dim k As Integer
 
For k = 1 To tabsize
    table(k) = Worksheets("Feuil1").Cells(l + k - tabsize, 1).Value
Next
 
' // Maintenant, on trie le tableau
' // Tri à bulle, pas terrible
Dim fini As Boolean
Dim buf As Double
 
Do
    ' // C'est déjà fini ...
    fini = True
    For k = 1 To tabsize - 1
        If table(k) > table(k + 1) Then
            ' // ... Ah, bah non.
            fini = False
            buf = table(k)
            table(k) = table(k + 1)
            table(k + 1) = buf
        End If
    Next
Loop While Not fini
 
' // la seconde plus petite valeur
MsgBox table(2)

En fait, on prend le problème à l'envers. On cherche à faire fonctionner une solution avant de chercher une solution.
Trier ton tableau est à la base une bonne idée. Mais si c'est juste pour une seule valeur, tu en fais trop !

Regarde ça un peu :

Dim zone  As Range
Dim cell0 As Range
Dim cell  As Range
Dim min1  As Double
Dim min2  As Double
 
Set cell0 = Worksheets("Feuil1").Cells(Rows.Count, 1).End(xlUp)
Set zone = Worksheets("Feuil1").Range(cell0.Offset(-99, 0), cell0)
 
min1 = zone.Cells(1).Value
min2 = zone.Cells(1).Value
 
For Each cell In zone
    If cell.Value < min1 Then
        min2 = min1
        min1 = cell.Value
    End If
Next
 
MsgBox min2

Effectivement, si c'est la n-ième plus petite valeur, il vaut mieux faire un tri.
Pour la première ou deuxième plus petite valeur, ça peut suffire.

voila merci bien pour votre aide en m'inspirant de tes données .en fait
il manque juste un detail tres important dans la macro à inclure
je veux changer de plage a chaque fois .je mexplique
lorsque je commence a la derniere ligne non vide de la colonne D(feuil1) j'ai un tableau de 100 valeurs et je trouve l'avant dernier nombre plus petit ,la macro le fait tres bien ,ensuite je veux que le tableau commence a la ligne precedent la derniere ligne non vide de la colonne D de la feuille("feuil1")
puis definir de la meme façon un tableau de taille 100 puis determiner l'avant dernier terme plus petit

bon j'ai introduit une boucle for mais j'ai l'avant dernier nombre du tableau qui commence a la derniere ligne non vide de la colonne D sur toute ma colonne
C (de C40 à C1)

et toi regarde ça

Sub determination_var_hist_cpr()

Dim wsh1 As Worksheet

Dim C As Range, plage As Range

Dim derligne As Long

Dim p As Double, pp As Double

Dim a As Long

Dim fin_tableau As Long

 a = Worksheets("Feuil2").Cells(Rows.Count, 7).End(xlUp).Row

Set wsh1 = Worksheets("Feuil1")

derligne = wsh1.Cells(Rows.Count, 4).End(xlUp).Row

For fin_tableau = derligne To 183 Step -1

Set plage = wsh1.Range("D" & derligne - 100 & ":D" & derligne)

p = plage(1): pp = plage(1) - 1

 
For Each C In plage

  If C.Value < pp Then

    p = pp: pp = C.Value

  ElseIf C.Value < p Then

    p = C.Value

  End If

Next

Worksheets("Feuil2").Cells(a, 3).Value = p

a = a - 1

 
derligne = derligne - 1

Next

Set wsh1 = Nothing: Set plage = Nothing: Set C = Nothing

 
End Sub

mais i y a un soucis
la colonne C de la feuil2 se rempli ,mon tableau comment a la ligne 2 mais j'ai ma cellule C2 vide  

merci d'avance