Add criteria to existing vba calculation that returns "N/A" if all responses in the category are N/A

[jbeets]

Hello,



The below calculation is used on a form that creates a score based on the responses in the combo boxes. In some cases all of the responses in a category may be "N/A" which then causes an error based on the way the current code is written. I need to modify the code to say that if all of the responses are "N/A" then return N/A in the CoverageScore box otherwise complete the calculation.

This is what I am trying to accomplish but I am getting an error saying "Else without If". I'm sure I'm missing something silly but I just dont know.

If nYes = 0 Then CoverageScore = "N/A"Else
CoverageScore = Format((nYes) / (nYes + nNo + nNA), "Percent")
End If

The full code is below:

Private Sub btnCoverageScore_Click()Dim c As Control, nYes As Long, nNo As Long, nNA As Long
nYes = 0
nNo = 0
nNA = 0




For Each c In Me.Controls
    If c.Tag = "Coverage" Then
        If c.Value = "Yes" Then nYes = nYes + 1
        If c.Value = "No" Then nNo = nNo + 1
        If c.Value = "NA" Then nNA = nNA + 1
    End If
Next c


CoverageScore = Format((nYes) / (nYes + nPartial + nNo + nNA), "Percent")


For Each c In Me.Controls
    If c.Tag = "Coverage" Then
        If IsNull(c.Value) Then
         MsgBox ("ComboBox selection left blank. Please ensure all drop downs are selected before continuing.")
    Else
        txtCoverageStatus = "Complete"
    End If
End If
Next c


For Each c In Me.Controls
    If c.Tag = "Status" Then
        If c.Value = "Complete" Then
        txtCoverageStatus.BackColor = vbGreen
    End If
End If
Next c
End Sub

Thank you for any help you can provide!

[Bob Fitz]

Try deleting the End If

For Each c In Me.Controls    If c.Tag = "Coverage" Then
        If IsNull(c.Value) Then
         MsgBox ("ComboBox selection left blank. Please ensure all drop downs are selected before continuing.")
    Else
        txtCoverageStatus = "Complete"
    End If
End If 
Next c

[jbeets]

I deleted the End If as suggested but I get the same error "Else without If" and it is highlighting this else:

If nYes = 0 Then CoverageScore = "N/A"Else
CoverageScore = Format((nYes) / (nYes + nNo + nNA), "Percent")
End If

[davegri]

Try it this way:

If nYes = 0 Then 
    CoverageScore = "N/A"
Else
    CoverageScore = Format((nYes) / (nYes + nNo + nNA), "Percent")
End If

[jbeets]

Thank you Davegri... That fixed my issue.

Thank you both for your help today!

[davegri]

Glad to help and thanks for the star!