End with without With Résolu

Question

Bonjour,
je trouve pas l'erreur dans mon code. Aidez moi SVP
Merci.


Public Function StockSecurite(Depot, Base_Oil, Transaction, Year, Week)
Dim i As Long
Dim j As Long
Dim w As Worksheet
Dim Sum As Double
Dim Max As Double
Set w = Worksheets("Donnees")
i = 0
j = 0
With w
While .Range("I2").Offset(i, 0) <> ""
If .Range("I2").Offset(i, -8) = Depot And .Range("I2").Offset(i, -7) = Base_Oil And .Range("I2").Offset(i, -5) = Transaction And .Range("I2").Offset(i, -2) = Year And .Range("I2").Offset(i, 0) = Week Then
While .Range("J2").Offset(i, 0) <> ""
      'determiner le stock d'une semaine
      While .Range("J2").Offset(j, 0) <> .Range("J2").Offset(i, 0) And .Range("J2").Offset(j, 0) <> ""
      If .Range("J2").Offset(i, 0) = .Range("J2").Offset(j, 0) Then
      Sum = Sum + .Range("J2").Offset(j, 0)
      End If
      j = j + 1
      Wend
      ' determiner le max de la consommation journaliere
      If Abs(.Range("J2").Offset(i, 0)) > Abs(.Range("J2").Offset(i + 1, 0)) Then
      Max = Abs(.Range("J2").Offset(i, 0))
      Else
      Max = Abs(.Range("J2").Offset(i + 1, 0))
      End If
i = i + 1
Wend
End With
StockSecurite = (Max - Sum / 7) * 7
End Function

Mytå · Answer

Salut le Forum

Code corrigé

Public Function StockSecurite(Depot, Base_Oil, Transaction, Year, Week)
    Dim i As Long
    Dim j As Long
    Dim w As Worksheet
    Dim Sum As Double
    Dim Max As Double
    Set w = Worksheets("Donnees")
    i = 0
    j = 0
    With w
        While .Range("I2").Offset(i, 0) <> ""
            If .Range("I2").Offset(i, -8) = Depot And .Range("I2").Offset(i, -7) = Base_Oil And .Range("I2").Offset(i, -5) = Transaction And .Range("I2").Offset(i, -2) = Year And .Range("I2").Offset(i, 0) = Week Then
                While .Range("J2").Offset(i, 0) <> ""
                    'determiner le stock d'une semaine
                    While .Range("J2").Offset(j, 0) <> .Range("J2").Offset(i, 0) And .Range("J2").Offset(j, 0) <> ""
                        If .Range("J2").Offset(i, 0) = .Range("J2").Offset(j, 0) Then
                            Sum = Sum + .Range("J2").Offset(j, 0)
                        End If
                        j = j + 1
                    Wend
                    ' determiner le max de la consommation journaliere
                    If Abs(.Range("J2").Offset(i, 0)) > Abs(.Range("J2").Offset(i + 1, 0)) Then
                        Max = Abs(.Range("J2").Offset(i, 0))
                    Else
                        Max = Abs(.Range("J2").Offset(i + 1, 0))
                    End If
                    i = i + 1
                Wend
            End If
        Wend
    End With
    StockSecurite = (Max - Sum / 7) * 7
End Function

Mytå

ccm81 · Answer

Bonjour

Peut être comme ça

Public Function StockSecurite(Depot, Base_Oil, Transaction, Year, Week)
Dim i As Long
Dim j As Long
Dim w As Worksheet
Dim Sum As Double
Dim Max As Double
Set w = Worksheets("Donnees")
i = 0
j = 0
With w
  While .Range("I2").Offset(i, 0) <> ""
    If Range("I2").Offset(i, -8) = Depot _
       And .Range("I2").Offset(i, -7) = Base_Oil _
       And .Range("I2").Offset(i, -5) = Transaction _
       And .Range("I2").Offset(i, -2) = Year _
       And .Range("I2").Offset(i, 0) = Week Then
         While .Range("J2").Offset(i, 0) <> ""
           'determiner le stock d'une semaine
            While .Range("J2").Offset(j, 0) <> .Range("J2").Offset(i, 0) And .Range("J2").Offset(j, 0) <> ""
              If .Range("J2").Offset(i, 0) = .Range("J2").Offset(j, 0) Then
                Sum = Sum + .Range("J2").Offset(j, 0)
              End If
              j = j + 1
            Wend
           ' determiner le max de la consommation journaliere
           If Abs(.Range("J2").Offset(i, 0)) > Abs(.Range("J2").Offset(i + 1, 0)) Then
             Max = Abs(.Range("J2").Offset(i, 0))
           Else
             Max = Abs(.Range("J2").Offset(i + 1, 0))
           End If
           i = i + 1
         Wend
    End If
  Wend
End With
StockSecurite = (Max - Sum / 7) * 7
End Function

Cdlmnt

eriiic · Answer

Bonjour à tous,

Tu devrais installer Smart Indenter, ça permet de voir ces types d'erreurs très facilement.
Bien que ça ne soit pas écrit fonctionne très bien jusqu'à excel 2013.

Je ne vais pas te mettre le code corrigé, c'est déjà fait. Mais ton code avec erreurs bien indenté :

Public Function StockSecurite(Depot, Base_Oil, Transaction, Year, Week)
    Dim i As Long
    Dim j As Long
    Dim w As Worksheet
    Dim Sum As Double
    Dim Max As Double
    Set w = Worksheets("Donnees")
    i = 0
    j = 0
    With w
        While .Range("I2").Offset(i, 0) <> ""
            If .Range("I2").Offset(i, -8) = Depot And .Range("I2").Offset(i, -7) = Base_Oil And .Range("I2").Offset(i, -5) = Transaction And .Range("I2").Offset(i, -2) = Year And .Range("I2").Offset(i, 0) = Week Then
                While .Range("J2").Offset(i, 0) <> ""
                    'determiner le stock d'une semaine
                    While .Range("J2").Offset(j, 0) <> .Range("J2").Offset(i, 0) And .Range("J2").Offset(j, 0) <> ""
                        If .Range("J2").Offset(i, 0) = .Range("J2").Offset(j, 0) Then
                            Sum = Sum + .Range("J2").Offset(j, 0)
                        End If
                        j = j + 1
                    Wend
                    ' determiner le max de la consommation journaliere
                    If Abs(.Range("J2").Offset(i, 0)) > Abs(.Range("J2").Offset(i + 1, 0)) Then
                        Max = Abs(.Range("J2").Offset(i, 0))
                    Else
                        Max = Abs(.Range("J2").Offset(i + 1, 0))
                    End If
                    i = i + 1
                Wend
            End With
            StockSecurite = (Max - Sum / 7) * 7
        End Function

Tu vois tout de suite où sont les pb. : il manque un end if et un wend

eric

End with without With

3 réponses

Discussions similaires

Newsletters