CODEWITHSUNDEEP
excelvba
MarkHav
MarkHav

Reputation: 53

ElseIf function not working when if statement used to open other workbooks

I am using an If/ElseIf statement to open diferent excel workbooks based on a value in a cell, so IF a value is somehting then the corresponding document opens.

    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

For some reason, after line 1 of the if statement, if the value is not "FWS Mag Seal replacement" then it goes straight to the end of the if statement, ignroing all other elseif statements.

Upvotes: 0

Views: 53

Answers (3)

VBasic2008
VBasic2008

Reputation: 54807

Snake ElseIf to For Next

The 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

Upvotes: 0

J.schmidt
J.schmidt

Reputation: 719

In this case I prefer to use the Case Select Function. It is more clear and tidies up your code a little bit:

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

If you stil like to use If Case Function I'd recommend you to write your code like this (you just forgot to put : after each Then):

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

Upvotes: 2

Brian M Stafford
Brian M Stafford

Reputation: 8868

You need to adjust your syntax a little bit. You are combining single line If statements with multi-line If statements and vba is getting confused. Try formatting like this instead:

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

Upvotes: 1

Related Questions