VBA *对于每个单元循环* IF（第 1 列中的值）= x AND IF（第 2 列中的等效值）>7 THEN
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42015478/
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
VBA *For Each Cell Loop* IF (Value in Column 1) = x AND IF (Equivalent Value In Column 2) >7 THEN
提问by Dan V
I am having an issue with VBA that I can't seem to find an answer to online. I only started teaching myself a couple of weeks ago so apologies if this is a fairly simple answer...
我在使用 VBA 时遇到了一个问题，我似乎无法在网上找到答案。我几周前才开始自学，所以如果这是一个相当简单的答案，我深表歉意......
I am trying to write a macro where the entire row is un-coloured (Is that a word??) based on a value in column E and the equivalent value in column AN (same row). What I have so far is:
我正在尝试根据 E 列中的值和 AN 列（同一行）中的等效值编写一个宏，其中整行都没有颜色（这是一个词吗？？）。到目前为止我所拥有的是：
For Each cell In Sheets(5).Range("E9:E" & LastRow)
If (cell.Value = "BA" Or cell.Value = "NH" Or cell.Value = "AD") Then
If ActiveCell.Offset(0, 35) > 7 Then
cell.EntireRow.Interior.color = xlNone
End If
Next cell
The only problem is, when I try to run it gives the 'Compile error: Next without For' error message.
唯一的问题是，当我尝试运行它时，会出现“编译错误：Next without For”错误消息。
?? Am I missing something... there is a 'For'...
?? 我是不是遗漏了什么......有一个'For'......
If I remove the below line then the code runs,
如果我删除以下行，则代码运行，
If ActiveCell.Offset(0, 35) > 7 Then
but it's not the output i require as all of the BA, NH & AD values in column A have their entire row un-coloured regardless of whether they are >7 or <7.
但这不是我需要的输出，因为 A 列中的所有 BA、NH 和 AD 值的整行都未着色，无论它们是 >7 还是 <7。
Is the error caused by the IF-THEN-IF statement?
是IF-THEN-IF语句导致的错误吗？
回答by Hexxed
Try this:
尝试这个：
For Each cell In Sheets(5).Range("E9:E" & LastRow)
If (cell.Value = "BA" Or cell.Value = "NH" Or cell.Value = "AD") Then
If ActiveCell.Offset(0, 35) > 7 Then
cell.EntireRow.Interior.color = xlNone
End If // You are missing this
End If
Next cell
回答by Shai Rado
You have another issue besides not closing the Ifwith End If.
除了不关闭Ifwith之外，您还有另一个问题End If。
- You are looping on
CellwithFor Each Cell In Sheets(5).Range("E9:E" & LastRow) - Then you are testing to see if
Cell.Value = "BA" or...etc - However, afterwards you are checking
If ActiveCell.Offset(0, 35) > 7 Then, I thinkActiveCellmeant to beCellas well.
- 您对循环
Cell用For Each Cell In Sheets(5).Range("E9:E" & LastRow) - 然后您正在测试以查看是否
Cell.Value = "BA" or...等 - 然而，事后要检查
If ActiveCell.Offset(0, 35) > 7 Then，我想ActiveCell意思是Cell也。
One last thing, instead of using multiple Or, you can use Select Case.
最后一件事，Or您可以使用Select Case.
Code
代码
For Each Cell In Sheets(5).Range("E9:E" & LastRow)
Select Case Cell.Value
Case "BA", "NJ", "AD"
If Cell.Offset(0, 35) > 7 Then
Cell.EntireRow.Interior.Color = xlNone
End If
End Select
Next cell
回答by user3598756
you could use AutoFilter():
你可以使用AutoFilter()：
With Sheets(5)
With .Range("E8", .Cells(.Rows.Count, "E").End(xlUp)).Resize(, 36)
.AutoFilter Field:=1, Criteria1:=Array("BA", "NJ", "AD"), Operator:=xlFilterValues
.AutoFilter Field:=36, Criteria1:=">7"
If Application.WorksheetFunction.Subtotal(103, .Cells) > 1 Then .Resize(.Rows.Count - 1).Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Interior.Color = xlNone
End With
.AutoFilterMode = False
End With
