Dev Faq
0
votes

La fonction ElseIf ne fonctionne pas lorsque l'instruction if est utilisée pour ouvrir d'autres classeurs

J'utilise une instruction If / ElseIf pour ouvrir différents classeurs Excel basés sur une valeur dans une cellule, donc SI une valeur est quelque chose, le document correspondant s'ouvre. 

    If ThisWorkbook.Sheets("Parts_List").Range("E2").Value = "FWS Mag Seal replacement" Then Workbooks.Open "C:\Users\UE294839\Documents\Off Site Project\Parts Lists\ARRIEL 2D FWS MAG SEAL REPLACEMENT.xlsx"
    ElseIf ThisWorkbook.Sheets("Parts_List").Range("E2").Value = "M04 S/E" Then Workbooks.Open "C:\Users\UE294839\Documents\Off Site Project\Parts Lists\ARRIEL 2B M04 REMOVA & REPLACE.xlsx"
    ElseIf ThisWorkbook.Sheets("Parts_List").Range("E2").Value = "Power Output Shaft Mag seal replacement" Then Workbooks.Open "C:\Users\UE294839\Documents\Off Site Project\Parts Lists\ARRIEL 2D output shaft mag seal replacement.xlsx"
    ElseIf ThisWorkbook.Sheets("Parts_List").Range("E2").Value = "Power Output Shaft Mag seal & Rear Bearing descaling" Then Workbooks.Open ""
    ElseIf ThisWorkbook.Sheets("Parts_List").Range("E2").Value = "Sealing Bush replacement" Then Workbooks.Open "C:\Users\UE294839\Documents\Off Site Project\Parts Lists\ARRIEL 2D Sealing bush replacement.xlsx"
    ElseIf ThisWorkbook.Sheets("Parts_List").Range("E2").Value = "TU 180" Then Workbooks.Open "C:\Users\UE294839\Documents\Off Site Project\Parts Lists\arriel 2b tu180.xlsx"
    ElseIf ThisWorkbook.Sheets("Parts_List").Range("E2").Value = "TU 181 - TU 198" Then Workbooks.Open "C:\Users\UE294839\Documents\Off Site Project\Parts Lists\ARRIEL 2B TU181-198.xlsx"
    ElseIf ThisWorkbook.Sheets("Parts_List").Range("E2").Value = "TU 201" Then Workbooks.Open "C:\Users\UE294839\Documents\Off Site Project\Parts Lists\ARRIEL 2E TU201 Parts requirement.xlsx"
    ElseIf ThisWorkbook.Sheets("Parts_List").Range("E2").Value = "TU 213" Then Workbooks.Open "C:\Users\UE294839\Documents\Off Site Project\Parts Lists\Arriel 2E TU213.xlsx"
    ElseIf ThisWorkbook.Sheets("Parts_List").Range("E2").Value = "TU 215" Then Workbooks.Open "C:\Users\UE294839\Documents\Off Site Project\Parts Lists\ARRIEL 2E TU215 rev1.xlsx"
    ElseIf ThisWorkbook.Sheets("Parts_List").Range("E2").Value = "TU213-215 (inc. Consumables)" Then Workbooks.Open ""
    End If

Pour une raison quelconque , après la ligne 1 de l'instruction if, si la valeur n'est pas "FWS Mag Seal replacement", elle va directement à la fin de l'instruction if, ignorant toutes les autres instructions elseif.

1 commentaires

C'est parce que ce code ne ne compile même pas . Vous ne pouvez pas utiliser une seule ligne d'instruction If suivie d'un ElseIf - If foo Then bar a un End If .

3 Réponses :

1
votes

Vous devez ajuster un peu votre syntaxe. Vous combinez des instructions If sur une seule ligne avec des instructions If sur plusieurs lignes et vba devient confus. Essayez plutôt de mettre en forme comme ceci: 

If ThisWorkbook.Sheets("Parts_List").Range("E2").Value = "FWS Mag Seal replacement" Then
   Workbooks.Open "C:\Users\UE294839\Documents\Off Site Project\Parts Lists\ARRIEL 2D FWS MAG SEAL REPLACEMENT.xlsx"
ElseIf ThisWorkbook.Sheets("Parts_List").Range("E2").Value = "M04 S/E" Then
   Workbooks.Open "C:\Users\UE294839\Documents\Off Site Project\Parts Lists\ARRIEL 2B M04 REMOVA & REPLACE.xlsx"
ElseIf ThisWorkbook.Sheets("Parts_List").Range("E2").Value = "Power Output Shaft Mag seal replacement" Then
   Workbooks.Open "C:\Users\UE294839\Documents\Off Site Project\Parts Lists\ARRIEL 2D output shaft mag seal replacement.xlsx"
ElseIf ThisWorkbook.Sheets("Parts_List").Range("E2").Value = "Power Output Shaft Mag seal & Rear Bearing descaling" Then
   Workbooks.Open ""
ElseIf ThisWorkbook.Sheets("Parts_List").Range("E2").Value = "Sealing Bush replacement" Then
   Workbooks.Open "C:\Users\UE294839\Documents\Off Site Project\Parts Lists\ARRIEL 2D Sealing bush replacement.xlsx"
ElseIf ThisWorkbook.Sheets("Parts_List").Range("E2").Value = "TU 180" Then
   Workbooks.Open "C:\Users\UE294839\Documents\Off Site Project\Parts Lists\arriel 2b tu180.xlsx"
ElseIf ThisWorkbook.Sheets("Parts_List").Range("E2").Value = "TU 181 - TU 198" Then
   Workbooks.Open "C:\Users\UE294839\Documents\Off Site Project\Parts Lists\ARRIEL 2B TU181-198.xlsx"
ElseIf ThisWorkbook.Sheets("Parts_List").Range("E2").Value = "TU 201" Then
   Workbooks.Open "C:\Users\UE294839\Documents\Off Site Project\Parts Lists\ARRIEL 2E TU201 Parts requirement.xlsx"
ElseIf ThisWorkbook.Sheets("Parts_List").Range("E2").Value = "TU 213" Then
   Workbooks.Open "C:\Users\UE294839\Documents\Off Site Project\Parts Lists\Arriel 2E TU213.xlsx"
ElseIf ThisWorkbook.Sheets("Parts_List").Range("E2").Value = "TU 215" Then
   Workbooks.Open "C:\Users\UE294839\Documents\Off Site Project\Parts Lists\ARRIEL 2E TU215 rev1.xlsx"
ElseIf ThisWorkbook.Sheets("Parts_List").Range("E2").Value = "TU213-215 (inc. Consumables)" Then
   Workbooks.Open ""
End If

1 commentaires

Ou utilisez : , par exemple: If Range ("E2"). Value = "FWS": Then ... le ElseIf au suivant La ligne fonctionnera maintenant car le : signifie qu'une autre instruction suit sur la même ligne.

2
votes

Dans ce cas, je préfère utiliser la fonction Case Select . C'est plus clair et ranger un peu votre code: 

If ThisWorkbook.Sheets("Parts_List").Range("E2").Value = "FWS Mag Seal replacement" Then: Workbooks.Open "C:\Users\UE294839\Documents\Off Site Project\Parts Lists\ARRIEL 2D FWS MAG SEAL REPLACEMENT.xlsx"
    ElseIf ThisWorkbook.Sheets("Parts_List").Range("E2").Value = "M04 S/E" Then: Workbooks.Open "C:\Users\UE294839\Documents\Off Site Project\Parts Lists\ARRIEL 2B M04 REMOVA & REPLACE.xlsx"
    ElseIf ThisWorkbook.Sheets("Parts_List").Range("E2").Value = "Power Output Shaft Mag seal replacement" Then: Workbooks.Open "C:\Users\UE294839\Documents\Off Site Project\Parts Lists\ARRIEL 2D output shaft mag seal replacement.xlsx"
    ElseIf ThisWorkbook.Sheets("Parts_List").Range("E2").Value = "Power Output Shaft Mag seal & Rear Bearing descaling" Then: Workbooks.Open ""
    ElseIf ThisWorkbook.Sheets("Parts_List").Range("E2").Value = "Sealing Bush replacement" Then: Workbooks.Open "C:\Users\UE294839\Documents\Off Site Project\Parts Lists\ARRIEL 2D Sealing bush replacement.xlsx"
    ElseIf ThisWorkbook.Sheets("Parts_List").Range("E2").Value = "TU 180" Then: Workbooks.Open "C:\Users\UE294839\Documents\Off Site Project\Parts Lists\arriel 2b tu180.xlsx"
    ElseIf ThisWorkbook.Sheets("Parts_List").Range("E2").Value = "TU 181 - TU 198" Then: Workbooks.Open "C:\Users\UE294839\Documents\Off Site Project\Parts Lists\ARRIEL 2B TU181-198.xlsx"
    ElseIf ThisWorkbook.Sheets("Parts_List").Range("E2").Value = "TU 201" Then: Workbooks.Open "C:\Users\UE294839\Documents\Off Site Project\Parts Lists\ARRIEL 2E TU201 Parts requirement.xlsx"
    ElseIf ThisWorkbook.Sheets("Parts_List").Range("E2").Value = "TU 213" Then Workbooks.Open "C:\Users\UE294839\Documents\Off Site Project\Parts Lists\Arriel 2E TU213.xlsx"
    ElseIf ThisWorkbook.Sheets("Parts_List").Range("E2").Value = "TU 215" Then: Workbooks.Open "C:\Users\UE294839\Documents\Off Site Project\Parts Lists\ARRIEL 2E TU215 rev1.xlsx"
    ElseIf ThisWorkbook.Sheets("Parts_List").Range("E2").Value = "TU213-215 (inc. Consumables)" Then: Workbooks.Open ""
End If

Si vous aimez toujours utiliser la fonction If Case , je vous recommande d'écrire votre code comme ceci (vous avez juste oublié de mettre : après chaque Puis):

Select Case ThisWorkbook.Sheets("Parts_List").Range("E2").Value
    Case "FWS Mag Seal replacement"
        Workbooks.Open "C:\Users\UE294839\Documents\Off Site Project\Parts Lists\ARRIEL 2D FWS MAG SEAL REPLACEMENT.xlsx"
    Case "M04 S/E"
        Workbooks.Open "C:\Users\UE294839\Documents\Off Site Project\Parts Lists\ARRIEL 2B M04 REMOVA & REPLACE.xlsx"
    Case "Power Output Shaft Mag seal replacement"
        Workbooks.Open "C:\Users\UE294839\Documents\Off Site Project\Parts Lists\ARRIEL 2D output shaft mag seal replacement.xlsx"
    Case "Power Output Shaft Mag seal & Rear Bearing descaling"
        Workbooks.Open ""
    Case "Sealing Bush replacement"
        Workbooks.Open "C:\Users\UE294839\Documents\Off Site Project\Parts Lists\ARRIEL 2D Sealing bush replacement.xlsx"
    Case "TU 180"
        Workbooks.Open "C:\Users\UE294839\Documents\Off Site Project\Parts Lists\arriel 2b tu180.xlsx"
    Case "TU 181 - TU 198"
        Workbooks.Open "C:\Users\UE294839\Documents\Off Site Project\Parts Lists\ARRIEL 2B TU181-198.xlsx"
    Case "TU 201"
        Workbooks.Open "C:\Users\UE294839\Documents\Off Site Project\Parts Lists\ARRIEL 2E TU201 Parts requirement.xlsx"
    Case "TU 213"
        Workbooks.Open "C:\Users\UE294839\Documents\Off Site Project\Parts Lists\Arriel 2E TU213.xlsx"
    Case "TU 215"
        Workbooks.Open "C:\Users\UE294839\Documents\Off Site Project\Parts Lists\ARRIEL 2E TU215 rev1.xlsx"
    Case "TU213-215 (inc. Consumables)"
        Workbooks.Open ""
End Select

0 commentaires
0
votes

Snake ElseIf to For Next

Le code 

Sub SelectCaseInsteadOfElseIf()

    Const cBooks As String = _
            "ARRIEL 2D FWS MAG SEAL REPLACEMENT.xlsx" _
            & "," & "ARRIEL 2B M04 REMOVA & REPLACE.xlsx" _
            & "," & "ARRIEL 2D output shaft mag seal replacement.xlsx" _
            & "," & "" _
            & "," & "ARRIEL 2D Sealing bush replacement.xlsx" _
            & "," & "arriel 2b tu180.xlsx" _
            & "," & "ARRIEL 2B TU181-198.xlsx" _
            & "," & "ARRIEL 2E TU201 Parts requirement.xlsx" _
            & "," & "Arriel 2E TU213.xlsx" _
            & "," & "ARRIEL 2E TU215 rev1.xlsx" _
            & "," & ""

    Const cCriteria As String = _
            "FWS Mag Seal replacement" _
            & "," & "M04 S/E" _
            & "," & "Power Output Shaft Mag seal replacement" _
            & "," & "Power Output Shaft Mag seal & Rear Bearing descaling" _
            & "," & "Sealing Bush replacement" _
            & "," & "TU 180" _
            & "," & "TU 181 - TU 198" _
            & "," & "TU 201" _
            & "," & "TU 213" _
            & "," & "TU 215" _
            & "," & "TU213-215 (inc. Consumables)"

    Const cPath As String = "C:\Users\UE294839\Documents\Off Site Project\" _
            & "Parts Lists\"
    Const cSheet As String = "Parts_List"
    Const cRange As String = "E2"

    Dim vntB As Variant
    Dim vntC As Variant
    Dim i As Integer

    vntB = Split(cBooks, ",")
    vntC = Split(cCriteria, ",")

    For i = 0 To UBound(vntC)
        If ThisWorkbook.Sheets(cSheet).Range(cRange).Value = vntC(i) Then
            Workbooks.Open cPath & vntB(i)
            Exit For
        End If
    Next

End Sub
0 commentaires

Articles qui pourrait vous intéresser :

Comment amener quatre colonnes de données Excel en deux lignes dans Pandas
AttributeError: l'objet 'ElementTree' n'a pas d'attribut 'getiterator' lors de la tentative d'importation d'un fichier Excel
La fenêtre de l'application EXCEL ne se ferme pas sur classeur.close ()
Exécuter la macro lorsque le résultat de la cellule change par formule