excel - 如何每 9 行在列数据上运行代码？

Question

我已经按照我想要的方式工作了。但是，它似乎在 K13:K5000 的 K 列中的所有行上运行。

如何使此代码仅在 K13 之后的每 9 行运行一次？

Dim xRg As Range

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("K13:K5000")) Is Nothing Then
        If IsDate(Target.Value) And Target.Value > 0 Then
            targetRow = Target.Row
            offsetRow = Target.Offset(9, 0).Row
            Call Mail_small_Text_Outlook(targetRow, offsetRow)
        End If
    End If
End Sub

Sub Mail_small_Text_Outlook(targetRow, offsetRow)
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    xMailBody = "Hello" & vbNewLine & vbNewLine & _
              "This client is now Committed & Complete and ready for your attention" & vbNewLine & vbNewLine & _
              "Renew As Is?" & vbNewLine & _
              "Adding Changing Groups?"
              
    On Error Resume Next
    With xOutMail
        .To = ""
        .CC = ""
        .BCC = ""
        .Subject = "Committed & Complete"
        .Body = xMailBody
        .Display   
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
End Sub

标签： excelvbaoutlook

Answer 1

运行后targetRow = Target.Row检查它是否是第 9 行。这可以使用 Mod 函数来执行，以得到 targetRow 的余数除以 9（当然是在减去 13 之后，因为您从第 13 行开始）。就像是

Dim bIsNinthRow as Boolean
Dim ModResult as Long
bIsNinthRow = False
ModResult = (targetRow - 13) Mod 9
If ModResult = 0 Then bIsNinthRow = True
If bIsNinthRow Then Call Mail_small_Text_Outlook(targetRow, offsetRow)