Multiple IF ELSE in VBA

Question

I'm trying to build code that goes through a series of If statements to assess the contents of 4 drop down lists in the spreadsheet itself.

However when I use the code below I just get an error saying

Block IF without End IF

which despite trying lots of alternatives can't seem to rid.

Apologies for the length of code here but feel it's necessary to illustrate what I'm trying to accomplish!

Sub GOClick()
Worksheets("Dashboard").Select
If Worksheets("Dashboard").Range("B3") = "National Gallery" And 
Worksheets("Dashboard").Range("B4") = "Unframed" And 
Worksheets("Dashboard").Range("B7") = "Product Costings" And 
Worksheets("Dashboard").Range("B5") = "N/A" And 
Worksheets("Dashboard").Range("B6") = "N/A" Then
Worksheets("(7b)").Activate
Range("A8:F23").Copy
Worksheets("Dashboard").Activate
Range("D11").PasteSpecial xlPasteAll


Else:
If Worksheets("Dashboard").Range("B3") = "N/A" And 
Worksheets("Dashboard").Range("B4") = "N/A" And 
Worksheets("Dashboard").Range("B7") = "Product Costings" And 
Worksheets("Dashboard").Range("B5") = "N/A" And 
Worksheets("Dashboard").Range("B6") = "Inkjet" Then
Worksheets("(1)").Activate
Range("A5:G69").Copy
Worksheets("Dashboard").Activate
Range("D11").PasteSpecial xlPasteAll


Else:
If Worksheets("Dashboard").Range("B3") = "N/A" And 
Worksheets("Dashboard").Range("B4") = "N/A" And 
Worksheets("Dashboard").Range("B7") = "Product Costings" And 
Worksheets("Dashboard").Range("B5") = "N/A" And 
Worksheets("Dashboard").Range("B6") = "Xerox" Then
Worksheets("(1)").Activate
Range("A5:I101").Copy
Worksheets("Dashboard").Activate
Range("D11").PasteSpecial xlPasteAll

Else:
If Worksheets("Dashboard").Range("B3") = "Wholesale" And 
Worksheets("Dashboard").Range("B4") = "Ready to Hang" And 
Worksheets("Dashboard").Range("B7") = "Product Costings" And 
Worksheets("Dashboard").Range("B5") = "N/A" And 
Worksheets("Dashboard").Range("B6") = "N/A" Then
Worksheets("(3a)").Activate
Range("A5:S105").Copy
Worksheets("Dashboard").Activate
Range("D11").PasteSpecial xlPasteAll
Else: MsgBox ("No Data")
End If

End Sub

Each time you put `Else: If` you are creating a nested `if` statment and since you only have one `end if` at the end of the block the program is throwing the error. @tigeravatar's comment should fix this. — Jarom, Sep 07 '17 at 14:42

iDevlop · Accepted Answer · 2017-09-07T20:25:16.400

Here is the corrected code, with all those ugly useless Activate removed, and proper indentation. I also added a With clause to make it faster and shorter.

Sub GOClick()
With Worksheets("Dashboard")
    If .Range("B3") = "National Gallery" And _
                .Range("B4") = "Unframed" And _
                .Range("B7") = "Product Costings" And _
                .Range("B5") = "N/A" And _
                .Range("B6") = "N/A" Then
        Worksheets("(7b)").Range("A8:F23").Copy
        .Range("D11").PasteSpecial xlPasteAll

    ElseIf .Range("B3") = "N/A" And _
                .Range("B4") = "N/A" And _
                .Range("B7") = "Product Costings" And _
                .Range("B5") = "N/A" And _
                .Range("B6") = "Inkjet" Then
        Worksheets("(1)").Range("A5:G69").Copy
        .Range("D11").PasteSpecial xlPasteAll      

    ElseIf .Range("B3") = "N/A" And _
                .Range("B4") = "N/A" And _
                .Range("B7") = "Product Costings" And _
                .Range("B5") = "N/A" And _
                .Range("B6") = "Xerox" Then
        Worksheets("(1)").Range("A5:I101").Copy
        .Range("D11").PasteSpecial xlPasteAll

    ElseIf .Range("B3") = "Wholesale" And _
                .Range("B4") = "Ready to Hang" And _
                .Range("B7") = "Product Costings" And _
                .Range("B5") = "N/A" And _
                .Range("B6") = "N/A" Then
        Worksheets("(3a)").Range("A5:S105").Copy
        .Range("D11").PasteSpecial xlPasteAll
    Else
        MsgBox ("No Data")
    End If
End With

End Sub

You can even shorten the copies to one line eg. `Worksheets("(7b)").Range("A8:F23").Copy .Range("D11")` I think (bad line break here in the comment now :/). Can't you? `.Copy` accepts an optional destination parameter which should be the same result as `xlPasteAll`. — Pᴇʜ, Sep 07 '17 at 14:51

score -1 · Answer 2 · answered Sep 07 '17 at 14:38

-1

Add End if to your code:

......
Range("D11").PasteSpecial xlPasteAll
Else: MsgBox ("No Data")
End If
End if
End Sub

answered Sep 07 '17 at 14:38

adarti

77
3

1

ehm one `End If` would not be enough. Although using `ElseIf` would be preferable compared to a nested `If` block. – Pᴇʜ Sep 07 '17 at 14:43

Multiple IF ELSE in VBA

2 Answers2