我目前正在嘗試使用 VBA 在整個列的總和大于 420 且小于 430（兩個條件）時自動發送電子郵件。現在，我有它，如果該列中的單元格的值大于 420 且小于 430（例如：425），則會發送電子郵件，但我似乎無法重定向代碼以查看總和整個列，而不僅僅是列中滿足此條件的單元格。
如果我要使用多個單元格的值加起來滿足此條件（例如：一個單元格為 415，另一個單元格為 7），則 VBA 代碼無法識別這兩個單元格的總和符合條件。
任何輸入表示贊賞！謝謝
代碼：
Dim xRg As Range
'Update by Extendoffice 2018/3/7
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Cells.Count > 1 Then Exit Sub
Set xRg = Intersect(Columns("C"), Target)
If xRg Is Nothing Then Exit Sub
If IsNumeric(Target.Value) And Target.Value >= 420 And Target.Value <= 430 Then
Call Mail_small_Text_Outlook
End If
End Sub
Sub Mail_small_Text_Outlook()
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
xMailBody = "The Employee named in the Subject of this email has exceeded 420 FMLA Hours. Please act accordingly. Thank you"
On Error Resume Next
With xOutMail
.To = "[email protected]"
.CC = ""
.BCC = ""
.Subject = [B2].Value
.Body = xMailBody
.Display 'or use .Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub
uj5u.com熱心網友回復：
如果 A 列中的總和超過 100，我能夠觸發一個 msgbox。也許，這個邏輯對你有用。
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Set xRg = Intersect(Columns("A"), Target)
If xRg Is Nothing Then Exit Sub
Dim GetSum As Double
GetSum = Application.WorksheetFunction.Sum(Target.Parent.Range("A:A"))
If GetSum > 100 Then
MsgBox "Triggered.", vbInformation
End If
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/380741.html
上一篇：根據單元格更改隱藏Excel列