Excel VBA IF ElseIf

Question

I keep on getting a 'mismatched type error', or some other menial error like 'no block if'. However when I fix one, the other pops back up. Not sure where the problem is in this code. It's pretty straight forward, get cell values and then classify based on the values.

Private Sub CommandButton2_Click()
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Set sh1 = ActiveWorkbook.Sheets("Completed Questionnaire")
Set sh2 = ActiveWorkbook.Sheets("Classifier")

If sh1.Range("C10") = "Yes" Or sh1.Range("C11") = "Yes" Then
    If sh1.Range("C19:C20") = "Yes" Or sh1.Range("C17") = "Yes" Then
        sh2.Unprotect Password:="xxx"
        sh2.Range("D4") = "PS1"
        sh2.Protect Password:="xxx"
    End If

ElseIf sh1.Range("C10") = "Yes" Or sh1.Range("C11") = "Yes" And _
    sh1.Range("C14") = "Yes" Or _
    sh1.Range("C15") = "Yes" Or _
    sh1.Range("C16") = "Yes" Or _
    sh1.Range("C18") = "Yes" Then
        sh2.Unprotect Password:="xxx"
        sh2.Range("D4") = "PS2"
        sh2.Protect Password:="xxx"

ElseIf sh1.Range("C12") = "Yes" And sh1.Range("C21") = "Yes" Then
    If sh1.Range("C10:C11") = "No" Then
        sh2.Unprotect Password:="xxx"
        sh2.Range("D4") = "PS3"
        sh2.Protect Password:="xxx"
    End If


ElseIf sh1.Range("C13") = "Vendor Only" And sh1.Range("C10:C11") = "Yes" 
Then _ 
    If ("C14") = "Yes" Or _
    sh1.Range("C15") = "Yes" Or _
    sh1.Range("C16") = "Yes" Or _
    sh1.Range("C17") = "Yes" Or _
    sh1.Range("C18") = "Yes" Or _
    sh1.Range("C19") = "Yes" Or _
    sh1.Range("C20") = "Yes" Then _
        sh2.Unprotect Password:="xxx"
        sh2.Range("D4") = "Payment Process"
        sh2.Protect Password:="xxx"
    End If

ElseIf sh1.Range("C10:C21") = "No" Then
    sh2.Unprotect Password:="xxx"
    sh2.Range("D4") = "Non-Payment"
    sh2.Protect Password:="xxx"

Else: sh2.Unprotect Password:="xxx"
    sh2.Range("D4") = "Needs Review"
    sh2.Protect Password:="xxx"

End If
Sheets("Classifier").Select

End Sub

Also you have a Then on a separate line here - is what SO has exactly as you have it in VBA? Also, try not to use the Else: ... and just put that part on a separate line too. — BruceWayne
– BruceWayne, Commented Sep 25, 2017 at 19:33

BruceWayne · Accepted Answer · 2017-09-25 20:15:20Z

Try this:

Private Sub CommandButton2_Click()
Dim sh1     As Worksheet
Dim sh2     As Worksheet
Set sh1 = ActiveWorkbook.Sheets("Completed Questionnaire")
Set sh2 = ActiveWorkbook.Sheets("Classifier")

If sh1.Range("C10") = "Yes" Or sh1.Range("C11") = "Yes" Then
    If sh1.Range("C19:C20") = "Yes" Or sh1.Range("C17") = "Yes" Then
        sh2.Unprotect Password:="xxx"
        sh2.Range("D4") = "PS1"
        sh2.Protect Password:="xxx"
    End If

ElseIf sh1.Range("C10") = "Yes" Or sh1.Range("C11") = "Yes" And _
       sh1.Range("C14") = "Yes" Or _
       sh1.Range("C15") = "Yes" Or _
       sh1.Range("C16") = "Yes" Or _
       sh1.Range("C18") = "Yes" Then
        sh2.Unprotect Password:="xxx"
        sh2.Range("D4") = "PS2"
        sh2.Protect Password:="xxx"

ElseIf sh1.Range("C12") = "Yes" And sh1.Range("C21") = "Yes" Then
    If sh1.Range("C10:C11") = "No" Then
        sh2.Unprotect Password:="xxx"
        sh2.Range("D4") = "PS3"
        sh2.Protect Password:="xxx"
    End If

ElseIf sh1.Range("C13") = "Vendor Only" And sh1.Range("C10:C11") = "Yes" Then
    If ("C14") = "Yes" Or _
       sh1.Range("C15") = "Yes" Or _
       sh1.Range("C16") = "Yes" Or _
       sh1.Range("C17") = "Yes" Or _
       sh1.Range("C18") = "Yes" Or _
       sh1.Range("C19") = "Yes" Or _
       sh1.Range("C20") = "Yes" Then
        sh2.Unprotect Password:="xxx"
        sh2.Range("D4") = "Payment Process"
        sh2.Protect Password:="xxx"
    End If

ElseIf sh1.Range("C10:C21") = "No" Then
    sh2.Unprotect Password:="xxx"
    sh2.Range("D4") = "Non-Payment"
    sh2.Protect Password:="xxx"
Else
    sh2.Unprotect Password:="xxx"
    sh2.Range("D4") = "Needs Review"
    sh2.Protect Password:="xxx"
End If
Sheets("Classifier").Select

End Sub

I fixed a few errant newlines and mixed _. Note, there's a better way to do your various Or statements. But first, can all those cells be Yes, or only one at a time, for you to want to unprotect and protect?

Edit: I think I cleaned it up a little, using COUNTIF(). See if this works:

Private Sub CommandButton2_Click()
Dim sh1     As Worksheet
Dim sh2     As Worksheet
Set sh1 = ActiveWorkbook.Sheets("Completed Questionnaire")
Set sh2 = ActiveWorkbook.Sheets("Classifier")

If WorksheetFunction.CountIf(sh1.Range("C10:C11", "C14"), "Yes") > 0 Then
    If WorksheetFunction.CountIf(sh1.Range("C19:C20", "C17"), "Yes") > 0 Then
        sh2.Unprotect Password:="xxx"
        sh2.Range("D4") = "PS1"
        sh2.Protect Password:="xxx"
    End If

ElseIf WorksheetFunction.CountIf(sh1.Range("C10:C11"), "Yes") > 0 And _
       WorksheetFunction.CountIf(sh1.Range("C14:C16", "C18"), "Yes") > 0 Then
    sh2.Unprotect Password:="xxx"
    sh2.Range("D4") = "PS2"
    sh2.Protect Password:="xxx"

ElseIf WorksheetFunction.CountIf(sh1.Range("C12, C21"), "Yes") > 0 Then
    If WorksheetFunction.CountIf(sh1.Range("C10:C11"), "No") > 0 Then
        sh2.Unprotect Password:="xxx"
        sh2.Range("D4") = "PS3"
        sh2.Protect Password:="xxx"
    End If

ElseIf sh1.Range("C13") = "Vendor Only" And WorksheetFunction.CountIf(sh1.Range("C10:C11"), "Yes") > 0 Then
    If WorksheetFunction.CountIf(sh1.Range("C14:C20"), "Yes") > 0 Then
        sh2.Unprotect Password:="xxx"
        sh2.Range("D4") = "Payment Process"
        sh2.Protect Password:="xxx"
    End If

ElseIf WorksheetFunction.CountIf(sh1.Range("C10:C21"), "No") > 0 Then
    sh2.Unprotect Password:="xxx"
    sh2.Range("D4") = "Non-Payment"
    sh2.Protect Password:="xxx"
Else
    sh2.Unprotect Password:="xxx"
    sh2.Range("D4") = "Needs Review"
    sh2.Protect Password:="xxx"
End If
Sheets("Classifier").Select

End Sub

Batma... I mean Master Wayne, I'd love to hear how to improve all the Or statements... and all the cells can potentially be "Yes", It doesn't have to be one at a time, I just need to check the contents of each cell, and as long as one of them contains a "Yes" it will execute the unprotect.
@Mdurocher - See the edited add'l code. I think that may help. I don't think it's possible to do Range("A1:A2") = "Yes" to see if either (or both) cells in that range are a value. You should get a Type Mismatch error if I'm not mistaken. Therefore I used Countif().

kolcinx · Accepted Answer · 2017-09-25 20:18:20Z

3

Comparing multiple cells at once

Comparing multiple cell values to other value is failing, e.g.:
sh1.Range("C19:C20") = "Yes"
What are you trying to do here? Test that both C19 AND C20 are = "Yes"?
Then (god forbid me for the code i am to write) use 2 comparisons:
sh1.Range("C19") = "Yes" AND sh1.Range("C20") = "Yes"

Syntax error

There is a syntax error in the 4th main IF block:
ElseIf sh1.Range("C13") = "Vendor Only" And sh1.Range("C10:C11") = "Yes" Then _
Should be:
ElseIf sh1.Range("C13") = "Vendor Only" And sh1.Range("C10:C11") = "Yes" Then

And to apply what we learned above, it should really be:
ElseIf sh1.Range("C13") = "Vendor Only" And sh1.Range("C19") = "Yes" AND sh1.Range("C20") = "Yes" Then

edited Sep 25, 2017 at 20:18

answered Sep 25, 2017 at 19:36

kolcinx

2,2431 gold badge17 silver badges38 bronze badges

Collectives™ on Stack Overflow

Excel VBA IF ElseIf

2 Answers 2

2 Comments

Comparing multiple cells at once

Syntax error

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comparing multiple cells at once

Syntax error

Comments

Your Answer

Sign up or log in

Post as a guest

Related