英文:
Loop through a column, while checking if cell is empty
问题
这段代码的主要目的是循环检查Excel表格中的某一列，如果满足一定条件（单元格为空且今天的日期减去表格中的日期大于等于7），则发送电子邮件。目前的问题是它跳过了某些单元格，而不是发送预期的5封电子邮件。
以下是翻译好的部分：
Private Sub Workbook_Open()
Dim myApp As Outlook.Application, mymail As Outlook.MailItem
Dim datum1 As Date
Dim datum2 As Long
Dim danasnji1 As Date
Dim danasnji2 As Long
Dim x As Long
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For x = 2 To lastrow
If IsEmpty(Cells(x, 10).Value) Then
x = x + 1
Else
datum1 = Cells(x, 10).Value
datum2 = datum1
danasnji1 = Date
danasnji2 = danasnji1
If danasnji2 - datum2 >= 7 Then
Set myApp = New Outlook.Application
Set mymail = myApp.CreateItem(olMailItem)
mymail.To = "examlemail@mail"
With mymail
.Subject = "Test"
.body = "Body Code"
.Send
End With
Cells(x, 10).Font.Color = RGB(255, 0, 0)
End If
End If
Next
Set myApp = Nothing
Set mymail = Nothing
End Sub
希望这能帮助您解决问题。如果仍然存在问题，您可能需要检查数据表格中的日期和条件是否正确，以确保所有符合条件的单元格都被处理。
英文:
I'm trying to loop through a column, while checking if cell in row is empty and if today's date minus date in table are higher or the same as 7. If requirements are met send email.
Private Sub Workbook_Open()
Dim myApp As Outlook.Application, mymail As Outlook.MailItem
Dim datum1 As Date
Dim datum2 As Long
Dim danasnji1 As Date
Dim danasnji2 As Long
Dim x As Long
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For x = 2 To lastrow
If IsEmpty(Cells(x, 10).Value) Then
x = x + 1
Else
datum1 = Cells(x, 10).Value
datum2 = datum1
danasnji1 = Date
danasnji2 = danasnji1
If danasnji2 - datum2 >= 7 Then
Set myApp = New Outlook.Application
Set mymail = myApp.CreateItem(olMailItem)
mymail.To = "examlemail@mail"
With mymail
.Subject = "Test"
.body = "Body Code"
.Send
End With
Cells(x, 10).Font.Color = RGB(255, 0, 0)
End If
End If
Next
Set myApp = Nothing
Set mymail = Nothing
End Sub
Picture of Excel table column:
This code sends email but the loop skips some cells. There should be 5 emails, but it sends 3. Cells that worked are coloured red.
答案1
得分: 2
Sure, here is the translated code:
永远不要在循环内修改计数变量（在您的情况下是 `x`），它会被 `For` 语句自动递增。
在您的情况下，您额外增加了它，导致代码在找到“空”行时跳过一行。将您的代码更改为
For x = 2 To lastrow
If Not IsEmpty(Cells(x, 10).Value) Then
datum1 = Cells(x, 10).Value
datum2 = datum1
danasnji1 = Date
danasnji2 = danasnji1
If danasnji2 - datum2 >= 7 Then
Set myApp = New Outlook.Application
Set mymail = myApp.CreateItem(olMailItem)
mymail.To = "examlemail@mail"
With mymail
.Subject = "Test"
.body = "Body Code"
.Send
End With
Cells(x, 10).Font.Color = RGB(255, 0, 0)
End If
End If
Next
请注意，您应该限定您的 `Cells` 使用：告诉 VBA 您要使用哪个工作表。如果您编写 `IsEmpty(Cells(x, 10).Value)`，VBA 将假定您要使用活动工作表，这并不是您始终希望的情况。详细查看 https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba
Please note that I've provided the translation for the code portion only, as per your request.
英文:
Never modify the counting variable (in your case x) inside the loop, it is incremented automatically by the For-statement.
In your case, you increment it additionally to the autoincrement, causing that the code skips a line when an "empty" line is found. Change your code to
For x = 2 To lastrow
If Not IsEmpty(Cells(x, 10).Value) Then
datum1 = Cells(x, 10).Value
datum2 = datum1
danasnji1 = Date
danasnji2 = danasnji1
If danasnji2 - datum2 >= 7 Then
Set myApp = New Outlook.Application
Set mymail = myApp.CreateItem(olMailItem)
mymail.To = "examlemail@mail"
With mymail
.Subject = "Test"
.body = "Body Code"
.Send
End With
Cells(x, 10).Font.Color = RGB(255, 0, 0)
End If
End If
Next
Note that you should qualify your Cells usages: Tell VBA with which worksheet you want to work with. If you write IsEmpty(Cells(x, 10).Value), VBA will assume that you want to work with the active sheet, and that is not always what you want. Have a close look to https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论