宏来检查一个特定范围的单元格是否包含值

问题描述：

我想在Excel中建立一个VB脚本宏来发送电子邮件。如果用户在列“F”中输入值为“否”，则脚本检查单元L2是否包含值“0”。如果它包含“1”或任何其他值，则不会提示发送电子邮件。除非我无法定义范围L2：L200，否则该脚本完美起作用。宏来检查一个特定范围的单元格是否包含值

这就是我想要做的：即，如果用户在列“F”的任何行中输入“否”并且列“L”中的同一行包含值为“0”，它应该会提示发送电子邮件。否则，它不应该。请有人帮助我，因为我在excel vb宏中只有有限的知识。 I have left a space between the code where the range is defined。

Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _ 
        (ByVal hwnd As Long, ByVal lpszOp As String, _ 
        ByVal lpszFile As String, ByVal lpszParams As String, _ 
        ByVal LpszDir As String, ByVal FsShowCmd As Long) _ 
        As Long 
------------------------------------------------------------------------------------------------------------------------- 
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) 
Dim lngResponse As Long 
Dim Mail_Object, Email_Subject, Email_Send_To, Email_Cc, Email_Bcc, Email_Body, Email_Body1, Email_Body2, Email_Body3 As String 
    If Left(Target.Address, 2) = "$F" Then 
    If Target.Value = "No" Then 


If Range("L2").Value = "0" Then 


    lngResponse = MsgBox("Draft and send an email now ?", vbYesNo) 
    If lngResponse = vbYes Then 
Email_Subject = "Approval needed to process change order for PO " & Range("$C" & Right(Target.Address, 2)).Value & "" 
    Email_Send_To = "" 
    Email_Cc = "" 
    Email_Bcc = "" 
    Email_Body = "Hi ," 
    Email_Body1 = " Please approve to process change order for PO# " & Range("$A" & Right(Target.Address, 2)).Value & ": " 
    Email_Body2 = " Actual price on the PO: $" & Range("$B" & Right(Target.Address, 2)).Value & "" 
    Email_Body3 = " , Vendor quoted price: $" & Range("$C" & Right(Target.Address, 2)).Value & "" & "  " & "  " & "  " & "  " & "  " & "  " & "  " & "  " 
    Mail_Object = "mailto:" & Email_Send_To & "?subject=" & Email_Subject & "&body=" & Email_Body & Email_Body1 & Email_Body2 & Email_Body3 & "&cc=" & Email_Cc '& "&bcc=" & Email_Bcc 
    'On Error GoTo debugs 
    ShellExecute 0&, vbNullString, Mail_Object, vbNullString, vbNullString, vbNormalFocus 
    End If 
    End If 
    End If 
    End If 
End Sub

什么是您的具体问题？哪一部分不按照你想要的方式行事？ – Banjoe

这不是VBScript，它是VBA。他们不是一回事。 –

答

对于任何行，固定单元格“L2”是否需要为0来提示输入电子邮件？或者你的意思是“如果更改的行中的L列为0，仅提示发送电子邮件”？如果是这样，下面的代码：

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) 
    'If column is F and entry is "no", case insensitive 
    If Target.Column = 6 And UCase(Target.Value) = "NO" Then 
     'If the L column on the changed cell's row equals 0 
     If Sh.Cells(Target.Row, 12) = 0 Then 
      'Prompt for email code here 
     End If 
    End If 
End Sub

编辑：错过了结束如果

宏来检查一个特定范围的单元格是否包含值

相关推荐