Excel VBA：在 ActiveSheet 中循环单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11918189/
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: Looping Though Cells In ActiveSheet
提问by Neat Machine
I'm trying to loop through a specified number of cells (defined by width and height), but I'm running into problems here. It keeps stalling on me, and then gets upset about:
我正在尝试遍历指定数量的单元格（由宽度和高度定义），但我在这里遇到了问题。它一直拖延在我身上，然后对以下事情感到不安：
If .Cells(11 + row, col).Value > maxVal Then
It's giving me an "Application defined or object defined error"
它给了我一个“应用程序定义或对象定义错误”
Can anyone tell me where I'm going wrong with my code:
谁能告诉我我的代码哪里出了问题：
Sub ApplyFilter()
Dim maxVal As Double
Dim minVal As Double
maxVal = ActiveSheet.Range("D10").Value
minVal = ActiveSheet.Range("D11").Value
Dim width As Integer
Dim height As Integer
width = ActiveSheet.Range("L3").Value
height = ActiveSheet.Range("L4").Value
Dim row As Integer
Dim col As Integer
ActiveSheet.Select
With Selection
row = 1
Do
col = 1
Do
If .Cells(11 + row, col).Value > maxVal Then
.Cells(11 + row, col).Value = 0
End If
If .Cells(11 + row, col).Value < minVal Then
.Cells(11 + row, col).Value = 0
End If
col = col + 1
width = width - 1
Loop Until width = 1
row = row + 1
height = height - 1
Loop Until height = 1
End With
End Sub
回答by Neat Machine
The problem here was that I was not resetting the width value for each new row. I should have been using a For loop, which would have handled the problem more intuitively.
这里的问题是我没有为每个新行重置宽度值。我应该使用 For 循环，它可以更直观地处理问题。
回答by GSerg
Sometimes Excel would give "Application defined or object defined error" for no reason, because it's just the way Excel is, but that's probably not the case here.
有时，Excel 会无缘无故地给出“应用程序定义或对象定义错误”，因为这正是 Excel 的方式，但这里可能并非如此。
Don't use Selector Selectionto interact with objects. Name the objects directly.
不要使用Select或Selection与对象交互。直接命名对象。
Dim ws As Worksheet
Set ws = ActiveSheet
With ws
...
End With
