vba 在excel vba中的行上循环并获取单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13882335/
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
loop on the rows in excel vba and get cell
提问by samimvp
I need help in Excel. My question is: How can I get the cell 42 of each row in this loop? As:
我需要 Excel 方面的帮助。我的问题是：如何获得此循环中每行的单元格 42？作为：
For Each r In Sheets("Sheet2").UsedRange.Rows
sval = r.Cells(42)
If sval = "" Then
If r.Cells(6).Value <> "" And r.Cells(7).Value <> "" And r.Cells(9).Value <> "" And r.Cells(10).Value <> "" And r.Cells(11).Value <> "" And r.Cells(12).Value <> "" Then
MsgBox "wtehi"
r.EntireRow.Interior.ColorIndex = 0
Else
MsgBox "yallow"
emptyMand = "ok"
r.EntireRow.Interior.ColorIndex = 6
End If
End If
Next
回答by Zenadix
To loop through all rows of worksheet wsand, for each row, get the cell on column 42, you can do this:
要遍历工作表的所有行，ws并为每一行获取第 42 列的单元格，您可以执行以下操作：
For Each rw in ws.UsedRange.Rows
cell = ws.Cells(rw.Row, 42)
Next
However, the method below is twice as fast, and more readable:
但是，下面的方法速度是原来的两倍，并且更具可读性：
For i = 1 to ws.UsedRange.Rows.Count
cell = ws.Cells(i, 42)
Next
回答by nutsch
Another recommended non-macro option being of course to use conditional formatting, I'd suggest this for the macro part:
另一个推荐的非宏选项当然是使用条件格式，我建议在宏部分这样做：
Dim cl As Range
For Each cl In Intersect(Sheets("Sheet2").UsedRange, Sheets("Sheet2").Columns(42))
If Len(cl) = 0 Then
If Application.WorksheetFunction.CountIf(Cells(cl.Row, 6).Resize(1, 5), "") <> 5 Then
MsgBox "wtehi"
cl.EntireRow.Interior.ColorIndex = 0
Else
MsgBox "yallow"
emptyMand = "ok"
cl.EntireRow.Interior.ColorIndex = 6
End If
End If
Next cl
