excel - 遍历 VBA (Excel) 中的列

Question

当我输入该标题​​时，我遇到了许多帮助主题，并查找了其中的几个并获得了一些基本概念。我仍然被困住，这就是为什么创建一个新帖子以获得一些指导。

场景是这样的:

我的 Excel 工作表在 A1 中有 Y 或 N 字母，在 B1 中有一些文本，在 C1 中有有效的 future 日期。

我目前有一个 VBA 脚本，它为单行执行此操作:

检查(C1 中的日期 -15 天)是否是今天的日期，然后检查 A1 是否为 N。如果这两个条件都为真，则发送自动电子邮件。

我不知道的是，如何使它适用于 n 行？

这是我现在所拥有的。

Sub SendAlert()
'Set up the objects required for Automation into lotus notes
    Dim Maildb As Object 'The mail database
    Dim UserName As String 'The current users notes name
    Dim MailDbName As String 'The current users notes mail database name
    Dim MailDoc As Object 'The mail document itself
    Dim AttachME As Object 'The attachment richtextfile object
    Dim Session As Object 'The notes session
    Dim EmbedObj As Object 'The embedded object (Attachment)
    Dim stSignature As String
    Dim i As Integer
    Dim lastRow As Long

    'Start a session to notes
    Set Session = CreateObject("Notes.NotesSession")

    'Get the sessions username and then calculate the mail file name
    'You may or may not need this as for MailDBname with some systems you
    'can pass an empty string
    UserName = Session.UserName
    MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"

    'Open the mail database in notes
    Set Maildb = Session.GETDATABASE("", MailDbName)
     If Maildb.IsOpen = True Then
          'Already open for mail
     Else
         Maildb.OPENMAIL
     End If

    'Set up the new mail document
    Set MailDoc = Maildb.CREATEDOCUMENT
    MailDoc.Form = "Memo"

    'Setting Flag to decide whether to send the mail or not
    mSend = 0

    'Copying Cells - Cell A1 as Copying Status , Cell B1 as Pending Tasks Cell C1 as Deadline Date
    cStatus = cStatus & Cells(1, 1)
    Msg = Msg & Cells(1, 2)
    dDate = dDate & Cells(1, 3)

    **'Looping through the cells**
    lastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row 'Gets the last row number
    For i = 1 To lastRow
        if & Cells(i,1) = "N"


    'Attach Your Signature
    stSignature = "Regards," & vbCrLf & "Some Name"

    'MailDoc.Recipient = Recipient
    Addressee = "someemailaddress@yahoo.com,"
    Recipient = Split(Addressee, ",")
    MailDoc.sendto = Recipient

    'MailDoc.Subject = Subject
    MailDoc.Subject = "Pending Activities Report"

    'MailDoc.Body = BodyText
    MailDoc.Body = "Dear NXC Team," & vbCrLf & vbCrLf & "Here are the pending activities:" & vbCrLf & vbCrLf & Msg & vbCrLf & vbCrLf & stSignature

    'MailDoc.SAVEMESSAGEONSEND = SaveIt
    MailDoc.SAVEMESSAGEONSEND = True

    'Send the document
    If (DateAdd("d", -15, dDate) = Date) And (cStatus = "N") Then
    MailDoc.SEND 0, Recipient
    End If

    'Clean Up
    Set Maildb = Nothing
    Set MailDoc = Nothing
    Set AttachME = Nothing
    Set Session = Nothing
    Set EmbedObj = Nothing
End Sub

循环遍历细胞是我无法理解的。我什至有逻辑..

检查 i 从 1 到最后一个非空白行的所有单元格(i,3)。

操作日期 - 单元格 (i,3) 中的每个数据为 15 天。

如果其中任何一个与今天匹配，则检查它对应的 (i,1) 列。

如果该特定 (i,1) 设置为 N，则触发电子邮件。

我有一个 mSend 标志。它最初为 0。如果满足第 4 个条件，那么我会将其设置为 1，然后在触发电子邮件之前检查 mSend 值。

请有人指导。

Answer 1

我自己明白了:)

lastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row 'Gets the last row number
For i = 1 To lastRow
    If DateAdd("d", -15, Cells(i, 3)) = Date Then
        If Cells(i, 1) = "N" Then
            mSend = 1
            Msg = Msg & Cells(i, 2)
        End If
    End If
Next i

谢谢!