英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论