Ecartype range dynamique

Bonjour,

Je cherche le moyen de calculer un écart type sur un range dynamique

Sub calcul()

For i = 5 To 18

    derniereligne = Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row
    n = 0
    m = 0
    x = 0

    For c = 1 To derniereligne
        If Sheets(2).Cells(c, 2) = Sheets(3).Cells(i, 3) And Sheets(2).Cells(c, 34) = 0 Then
            x = x + 1
            n = n + 1
            m = m + Sheets(2).Cells(c, 31)
            Sheets(5).Cells(x, i) = Sheets(2).Cells(c, 31)
        End If
    Next

    Sheets(3).Cells(i, 4) = n
    Sheets(3).Cells(i, 5) = m / n
    Sheets(3).Cells(i, 6) = Application.StDev(Sheets(3).Range(Cells(1, i), Cells(x, i)))

Next

End Sub

J'ai essayé cela mais cela me ressort une erreur. Auriez-vous une idée ?

Bonjour,

Essayez ceci

Sub calcul()
    Dim f2 As Worksheet, f3 As Worksheet, f5 As Worksheet
    Dim i As Long, n As Long, m As Long, x As Long
    Set f2 = Sheets(Sheets(2).Name)
    Set f3 = Sheets(Sheets(3).Name)
    Set f5 = Sheets(Sheets(5).Name)
    For i = 5 To 18
        derniereligne = f2.Cells(Rows.Count, 1).End(xlUp).Row
        n = 0
        m = 0
        x = 0
        For c = 1 To derniereligne
            If f2.Cells(c, 2) = f3.Cells(i, 3) And f2.Cells(c, 34) = 0 Then
                x = x + 1
                n = n + 1
                m = m + f2.Cells(c, 31)
                f5.Cells(x, i) = f2.Cells(c, 31)
            End If
        Next
        With f3
            .Cells(i, 4) = n
            .Cells(i, 5) = m / n
            .Cells(i, 6) = Application.StDev(Range(f3.Cells(1, i), f3.Cells(x, i)))
        End With
    Next
    Set f2 = Nothing
    Set f3 = Nothing
    Set f5 = Nothing
End Sub

Cdlt

Parfait merci !