lundi 25 septembre 2017

Excel VBA IF ElseIf

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

Aucun commentaire:

Enregistrer un commentaire