vba 循环遍历一系列单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8579899/
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
looping through a range of cells
提问by James
I need to run the function AddColumnofData on each cell from c27 and every contiguous non empty cell to the right, but I get a "Run-time error 424 object required", any help woul dbe greatly appreciated.
我需要在 c27 中的每个单元格和右侧的每个连续非空单元格上运行函数 AddColumnofData，但是我得到一个“需要运行时错误 424 对象”，任何帮助都将不胜感激。
Set col = Range("$C", Range("$C").End(xlToRight))
For Each c In col
AddColumnofData (c)
Next c
回答by chris neilsen
On the assumption you have defined AddColumnofDataas
假设您已定义AddColumnofData为
Sub AddColumnofData(c As Range)
...
End Sub
your call to it needs to be
你对它的呼吁必须是
AddColumnofData c
(that is, remove the brackets)
（即去掉括号）
Jesse's advice to DIMyour variables, while not not manditory is good advice. Applies to colas well. To make it manditory add Option Explicitto the top of your module.
杰西对DIM你的变量的建议，虽然不是强制性的，但这是个好建议。也适用于col。要使其成为强制添加Option Explicit到模块的顶部。
回答by Eric
You can declare or set your range objects before passing them to your function. To prove that you are passing the correct values to your function, try this.
您可以在将范围对象传递给函数之前声明或设置范围对象。要证明您将正确的值传递给您的函数，请尝试此操作。
Dim r As Range '-- if you don't declare it as a range type you get a variant type as default
Dim c As Range '-- this is used to store the single cell in the For Each loop
Set r = Range("A1:D1") '-- substitute your range as per your example
For Each c In r '-- you could also use r.cells
MsgBox c.Value '-- pass to your function instead of a call to the Message Box
Next
This produces a series of 4 Message Boxes containing the values in cells A1 to D1 of the active worksheet, if your Range "r" is seriously large then pass it to Debug.Print instead.
这将生成一系列 4 个消息框，其中包含活动工作表的单元格 A1 到 D1 中的值，如果您的范围“r”非常大，则将其传递给 Debug.Print。
回答by Jesse
If you declare c:
如果您声明 c：
Dim c as Range
Then what you have should work.
那么你所拥有的应该工作。
