嵌套的 If 语句在 VBA 中未返回正确的消息。

huangapple go评论76阅读模式
英文:

Nested If Statement not Returning Correct Message in VBA

问题

我正在尝试执行此VBA代码,以检查工作表,确保首先始终要求的单元格不为空(H6、X6、AH6、J8),然后检查单元格AN8中的TER代码是否存在,如果找到,则要检查附加单元格(AA19、AA20和J28)是否为空。我将此代码放在Sheet1上,并设置为在保存时执行。我没有收到任何错误消息,但即使在单元格AN8中找不到TER,此代码仍然显示我的第二个消息框:“终止所需字段丢失...”我漏掉了什么?

Sub CheckRequired()
    Dim celltxt As String
    celltxt = Sheet1.Range("AN8").Text

    If (Range("H6") = Empty) Or (Range("X6") = Empty) Or (Range("AH6") = Empty) Or (Range("J8") = Empty) Then
        MsgBox "必填字段未填写,请在突出显示的字段中输入缺失的信息,然后再次保存。"
    ElseIf InStr(1, celltxt, "TER") Then
    ElseIf (Range("AA19") = Empty) Or (Range("AA20") = Empty) Or (Range("J28") = Empty) Then
        MsgBox "终止所需字段丢失,请检查突出显示的必填字段。"
    Else
        MsgBox "人员变更表格已成功完成!"
    End If
End Sub
英文:

I am trying to have this VBA code execute checks on a sheet to make sure that first the always required cells are not empty(H6,X6,AH6,J8), then check for the TER code in cell AN8 if it is found I then want the additional cells (AA19,AA20 and J28) to be checked if they are empty. I have this code on sheet1, and have it setup to executed upon saving. I'm not getting any errors but this code keeps displaying my second msgbox of "A required field for a termination is missing..." even when TER is not found in cell AN8. What am I missing?

Sub CheckRequired()
    Dim celltxt As String
    celltxt = Sheet1.Range("AN8").Text

    If (Range("H6") = Empty) Or (Range("X6") = Empty) Or (Range("AH6") = Empty) Or (Range("J8") = Empty) Then
        MsgBox " A required field has not been populated, please enter the missing information in the highlighted field and save again. "
    ElseIf InStr(1, celltxt, "TER") Then
    ElseIf (Range("AA19") = Empty) Or (Range("AA20") = Empty) Or (Range("J28") = Empty) Then
        MsgBox " A required field for a Terminiation is missing, check highlighted required fields."
    Else
        MsgBox " The personnel change form has been successfully completed! "
    End If
End Sub

答案1

得分: 0

我可以将代码类似于这样进行工程化吗?

Sub CheckRequired()
    Dim celltxt As String
    celltxt = Sheet1.Range("AN8").Text

    If (Range("H6") = Empty) Or (Range("X6") = Empty) Or (Range("AH6") = Empty) Or (Range("J8") = Empty) Then
        MsgBox "一个必填字段未填写,请在突出显示的字段中输入缺少的信息,然后再次保存。"
        Exit Sub
    End If

    If InStr(1, celltxt, "TER") Then
        If (Range("AA19") = Empty) Or (Range("AA20") = Empty) Or (Range("J28") = Empty) Then
            MsgBox "终止所需字段缺失,请检查突出显示的必填字段。"
            Exit Sub
        End If
    End If

    MsgBox "人员变更表单已成功完成!"
End Sub

请注意,我已将代码翻译成中文,但保留了变量和函数名不变。如果您有其他需要,请告诉我。

英文:

Could you engineer the code in a way similar to this?

Sub CheckRequired()
    Dim celltxt As String
    celltxt = Sheet1.Range("AN8").Text

    If (Range("H6") = Empty) Or (Range("X6") = Empty) Or (Range("AH6") = Empty) Or (Range("J8") = Empty) Then
        MsgBox " A required field has not been populated, please enter the missing information in the highlighted field and save again. "
        Exit Sub
    End If
        
    If InStr(1, celltxt, "TER") Then
        If (Range("AA19") = Empty) Or (Range("AA20") = Empty) Or (Range("J28") = Empty) Then
            MsgBox " A required field for a Terminiation is missing, check highlighted required fields."
            Exit Sub
        End If
    End If

    MsgBox " The personnel change form has been successfully completed! "

End Sub

huangapple
  • 本文由 发表于 2020年1月4日 01:17:43
  • 转载请务必保留本文链接:https://go.coder-hub.com/59582693.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定