每个循环嵌套的excel vba
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18079495/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
excel vba nested for each loop
提问by jhakas
I am working on excel macro and got stuck at one point. Need help to please resolve it.
我正在处理 excel 宏并一度陷入困境。需要帮助来解决它。
I have to look for 2 rows in sheet, and for each value in 1 row look for the cell value in 2 row. If the range of values in row 2 equal to some conditional value, then come out of the row 2 check and set the flag as true. For achieving this I used two For Eachloop:
我必须在工作表中查找 2 行，并为 1 行中的每个值查找 2 行中的单元格值。如果第 2 行中的值范围等于某个条件值，则从第 2 行检查出来并将标志设置为真。为了实现这一点，我使用了两个For Each循环：
Sub Sendmail ()
For Each cell in Rows("5").Cells.SpecialCells(xlCellTypeConstant)
If cells.Value Like "*@*" Then
Subj = "Fill the Sheet"
Recipient = cell.Offset(0,-3).Value
EmailAddr = cell.Offset.Value
For Each row In Sheet14.Range("O244:AK244").Cells
If Not row = '8.00" Then
found = False
Else
found = True
End If
Next row
If found = False Then
Msg = "Hi " & Recipient & vbCrLf & vbCrLf
Msg = Msg & " Please fill the sheet for this week " & vbCrLf & vbCrLf
Set MItem = Outlook.CreateItem(oIMailItem)
With MItem
.To = EmailAddr
.Subject = Subj
.Body = Msg
.Save
End With
End If
End If
Next
End Sub
The foundvariable used here is defined as Boolean , but I am not able to use it properly, and every time found = falseis executing . I want only once the condition is true for row 2, then only the mail should be created .
found这里使用的变量定义为 Boolean ，但我无法正确使用它，并且每次都found = false在执行 . 我只想要第 2 行的条件为真，然后只应创建邮件。
回答by Siddharth Rout
Few things that I noticed... I have not tested the code but here are my general observations.
我注意到的几件事......我没有测试代码，但这里是我的一般观察。
A)One glaring piece of code is If cells.Value Like "*@*" Then. Change it to If cell.Value Like "*@*" Then
A)一段明显的代码是If cells.Value Like "*@*" Then. 将其更改为If cell.Value Like "*@*" Then
B)Change xlCellTypeConstantto xlCellTypeConstants
B）更改xlCellTypeConstant到xlCellTypeConstants
C)EmailAddr = cell.Offset.ValueIf you want to pick up the same cell's value, you don't need Offsetelse specify the parameters of Offset
C)EmailAddr = cell.Offset.Value如果要取相同单元格的值，则无需Offset指定 Offset 参数
D)@ChrisProsser has already commented on For Each row In Sheet14.Range("O244:AK244").Cells
D）@ChrisProsser 已经评论过了For Each row In Sheet14.Range("O244:AK244").Cells
E)For God's sake (well ignore that... For your sake), Use Option Explicit! I would strongly advise using Option ExplicitI would also recommend having a look at this link (SEE POINT 2 in the link).
E）看在上帝的份上（忽略那个...看在你的份上），使用Option Explicit！我强烈建议使用Option Explicit我还建议查看此链接（请参阅链接中的第 2 点）。
Topic: To ‘Err' is Human
话题: 'Err' 是人类
Link: http://www.siddharthrout.com/2011/08/01/to-err-is-human/
链接：http: //www.siddharthrout.com/2011/08/01/to-err-is-human/
回答by MakeCents
You are running that For loop;
你正在运行那个 For 循环；
For Each row In Sheet14.Range("O244:AK244").Cells
If Not row = '8.00" Then
found = False
Else
found = True
End If
Next row
for the entire range without doing anything per condition. This would be the same as only checking the last cell in the range, which is probably True, so thats why you think False is executing. Perhaps your if statement should be in this loop as well? Maybe where found = False is?
对于整个范围，无需为每个条件做任何事情。这与仅检查范围中的最后一个单元格相同，这可能是 True，所以这就是您认为 False 正在执行的原因。也许你的 if 语句也应该在这个循环中？也许找到 = False 在哪里？
