vba 具有非连续范围的 for 循环
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/601830/
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
For loop with non-contiguous range
提问by Chris Gunner
I have a loop that runs through each column and sets the value to an R1C1 sum of a few rows above it. I'm using a for loop, but I want to skip a few columns, as they contain formulae already in the cell. How can I set up a loop that only cycles through a non-contiguous set or numbers?
我有一个循环遍历每一列并将值设置为其上方几行的 R1C1 总和。我正在使用 for 循环，但我想跳过几列，因为它们包含单元格中已有的公式。如何设置仅循环遍历非连续集合或数字的循环？
For reference, I want it to cycle through columns 1 to 80, but skip cols 25, 36, 37, 44, 60, 63, 64, 67, 68, 73, 75 and 76.
作为参考，我希望它循环第 1 列到第 80 列，但跳过第 25、36、37、44、60、63、64、67、68、73、75 和 76 列。
Edit: thanks guys, but I;ve already got it working as you described; I was looking for a shorter and more elegant method.
编辑：谢谢你们，但我已经按照你的描述工作了；我正在寻找一种更短、更优雅的方法。
Edit 2: Is that even VBA?!
编辑 2：那甚至是 VBA 吗？！
回答by Adam Ralph
A VBA version of Learning's C# answer:-
学习的 C# 答案的 VBA 版本：-
Dim col As Integer: For col = 1 To 70
Select Case col
Case 25, 36, 37, 44, 60, 63, 64, 67, 68, 73, 75, 76
'do nothing'
Case Else
'do something'
End Select
Next col
回答by Renze de Waal
You can set up an array containing the column numbers that you need to process.
您可以设置一个包含需要处理的列号的数组。
Your loop can then loop over the array values.
然后您的循环可以遍历数组值。
Another solution is to define a boolean array with a value for every column and mark the columns that you want to skip. In the loop over all the columns check the boolean array.
另一种解决方案是定义一个布尔数组，每个列都有一个值，并标记要跳过的列。在所有列的循环中检查布尔数组。
Yet another solution is if you can detect in the loop whether the column is a column that you need to skip (by detecting the formula that is there).
另一种解决方案是，如果您可以在循环中检测该列是否是您需要跳过的列（通过检测存在的公式）。
回答by paxdiablo
I'd do one of two things. The first is to only execute the body for specific loop numbers:
我会做两件事之一。第一种是仅针对特定循环编号执行主体：
for i = 1 to 70
skipIt = false
skipIt = skipIt or (i = 25)
: : :
skipIt = skipIt or (i = 76)
if not skipIt then
' Put your body here. '
end if
next
Or, you can jump straight to the next:
或者，您可以直接跳到下一个：
for i = 1 to 70
if i = 25 goto nextIter
: : :
if i = 76 goto nextIter
' Put your body here. '
nextIter:
next
回答by Brann
you could put an if in the beginning of your loop, and skip the columns you don't want to iterate through
您可以在循环的开头放置一个 if，并跳过您不想遍历的列
回答by Learning
for i = 1 to 70
{
switch (i)
{
case 25, 36, 37, 44, 60, 63, 64, 67, 68, 73, 75 76 : break;
default : // process break;
}
}
回答by Learning
This approach uses Excel's range object functionality.
此方法使用 Excel 的范围对象功能。
Define your range using Excel's UNION method to be a range of non-contiguous of columns.
使用 Excel 的 UNION 方法将范围定义为非连续列的范围。
To speed up the loop, you can reduce the range to include only those cells which contain formulas using the SpecialCells method of the range object.
为了加速循环，您可以使用范围对象的 SpecialCells 方法缩小范围以仅包括那些包含公式的单元格。
Function LoopColumns() Dim Target As Range Dim Cell As Range
函数 LoopColumns() 将目标变暗为范围将单元格变暗为范围
' Create a range object based on the Union of the columns you want Set Target = Union(Range("A:D"), Range("F:G"), Range("J:L"), Range("P:Q"))
' 根据所需列的并集创建一个范围对象 Set Target = Union(Range("A:D"), Range("F:G"), Range("J:L"), Range("P :Q"))
' If you only want to process formulas, reduce the range again but having ' Excel define the range as only those cells which contain a formula Set Target = Target.SpecialCells(xlCellTypeFormulas, 1)
' 如果您只想处理公式，请再次缩小范围，但让 ' Excel 将范围定义为仅包含公式的单元格 Set Target = Target.SpecialCells(xlCellTypeFormulas, 1)
For Each Cell In Target.Cells
'process cells here...
Next Cell
End Function
结束函数
