尽管我在网上找到了许多建议和变体,但我一直在努力弄清楚这一点。如果用户忘记输入值或者可以在字符串范围内找到值,我想退出sub,这与用户表单有关。
以下是我为IF
子句尝试的许多变体中的最新变体:
Private Sub cmd_nProj_Click()
Dim wb As Workbook
Dim ws As Worksheet, ws_h As Worksheet
Dim i_rc As Long
Dim r_home As Range, r_ProdName As Range
Set wb = Application.ThisWorkbook
Set ws_h = wb.Sheets("Control")
i_rc = ws_h.Cells(Rows.Count, 1).End(xlUp).Row
Set r_home = ws_h.Range("A" & i_rc + 1)
Set r_ProdName = ws_h.Range("N2:N30")
If Me.tb_NewProjName = "" Or r_ProdName.Find(What:=Me.tb_NewProjName.Value, LookIn:=xlValues) Is Nothing Then
MsgBox ("Either you have left the projection name blank or the projection name is already being used, please try again!")
Exit Sub
End If
end sub
其他变体我会得到一个块变量没有设置错误的对象,在这个迭代中,if子句不能正常工作,因为即使文本框值出现在该范围内,if子句也会被跳过。
提前感谢!
发布于 2019-02-21 03:40:51
即使文本框值出现在该范围内,if子句也会被跳过。
这是因为当您在范围中查找don‘t值时,您正在退出sub。我已经添加了一个Not
来解决这个问题。
Private Sub cmd_nProj_Click()
Dim wb As Workbook
Dim ws As Worksheet, ws_h As Worksheet
Dim i_rc As Long
Dim r_home As Range, r_ProdName As Range
Set wb = Application.ThisWorkbook
Set ws_h = wb.Sheets("Control")
i_rc = ws_h.Cells(Rows.Count, 1).End(xlUp).Row
Set r_home = ws_h.Range("A" & i_rc + 1)
Set r_ProdName = ws_h.Range("N2:N30")
If Me.tb_NewProjName = "" Or Not (r_ProdName.Find(What:=Me.tb_NewProjName.Value, LookIn:=xlValues) Is Nothing) Then
MsgBox ("Either you have left the projection name blank or the projection name is already being used, please try again!")
Exit Sub
End If
End Sub
https://stackoverflow.com/questions/54793937
复制相似问题