时间戳

首页 > 解决方案 > If Statement for Error Checking is not working

excel - If Statement for Error Checking is not working

问题描述

I need to do some error checking so a user knows they have missed some information before sending the form.

I have a table which is controlled by a Data Validation list dropdown box. This box has numbers 1 to 15, and will show/hide rows associated to the number selected i.e. 3 selected, so rows 1 to 3 are displayed, 4 to 15 or hidden. What the error checking code is supposed to do is, check the list number selected, then check a cell in each row to see if it is empty or not, if empty display the error message, if not then do nothing. I need this error message to display for each row which is displayed and not for the hidden rows. I have a cell at the top of the page with tells the user how many errors they have, so I don't want the hidden rows adding to this count. I can get it working for numbers zero and 1, but for 2 to 15 it is not working. Code below only shows numbers 0 to 3, I can alter the code for the other rows once working.

Correct outcome when nbr 3 is selected D63 is empty so error msg in A63, D64:65 not empty so no error msg Error

Actual outcome when Nbr 3 is selected D63:65 are empty so A63:65 should display the error msg Outcome

Sub Check_SRU_Table()

    ' SRU 0 ** WORKING **
    If Range("SRUAdd").Value = "0" Then
        Range("Sig_1").Value = "Signature Release 1"
        Range("Sig_2").Value = "Signature Release 2"
        Range("Sig_3").Value = "Signature Release 3"
    ' 4 TO 15 OMITTED
    End If


    ' SRU 1 ** WORKING **
    If Range("SRUAdd").Value = "1" Then
        If Range("SRUName1").Value = "" Then
            Range("Sig_1").Value = "Signature Release 1 - ERROR"
        Else
            Range("Sig_1").Value = "Signature Release 1"
        End If
    End If

    ' SRU 2 ** NOT WORKING **
    If Range("SRUAdd").Value = "2" Then
        If Range("SRUName1").Value = "" Then
            Range("Sig_1").Value = "Signature Release 1 - ERROR"
        ElseIf Range("SRUName2").Value = "" Then
            Range("Sig_2").Value = "Signature Release 2 - ERROR"
        Else
            Range("Sig_1").Value = "Signature Release 1"
            Range("Sig_2").Value = "Signature Release 2"
        End If
    End If

    ' SRU 3 ** NOT WORKING **
    If Range("SRUAdd").Value = "3" Then
        If Range("SRUName1").Value = "" Then
            Range("Sig_1").Value = "Signature Release 1 - ERROR"
        ElseIf Range("SRUName2").Value = "" Then
            Range("Sig_2").Value = "Signature Release 2 - ERROR"
        ElseIf Range("SRUName3").Value = "" Then
            Range("Sig_3").Value = "Signature Release 3 - ERROR"
        Else
            Range("Sig_1").Value = "Signature Release 1"
            Range("Sig_2").Value = "Signature Release 2"
            Range("Sig_3").Value = "Signature Release 3"
        End If
    End If
    End Sub

    Private Sub BtnCheck_Click()
        Check_SRU_Table
    End Sub

Any help to figure out where I'm going wrong would be much appreciated.

标签： excelvba

解决方案

Only one condition of your If...ElseIf...Else block will be met. Sounds like you want to check all conditions. Therefore, you'd need to rewrite to something like this:

If Range("SRUAdd").Value = "3" Then
    If Range("SRUName1").Value = "" Then
        Range("Sig_1").Value = "Signature Release 1 - ERROR"
    End If
    If Range("SRUName2").Value = "" Then
        Range("Sig_2").Value = "Signature Release 2 - ERROR"
    End If
    If Range("SRUName3").Value = "" Then
        Range("Sig_3").Value = "Signature Release 3 - ERROR"
    End If
    If Range("SRUName3").Value <> "" And Range("SRUName2").Value <> "" And Range("SRUName1").Value <> "" Then
        Range("Sig_1").Value = "Signature Release 1"
        Range("Sig_2").Value = "Signature Release 2"
        Range("Sig_3").Value = "Signature Release 3"
    End If
End If

I'm sure there's a more elegant way to do it, but this version requires the least rewrite of how you've chosen to approach it.

推荐阅读