Excel VBA - 对范围内的每个单元格执行函数

Excel VBA - Performing function to each cells in range

提问by user1130306

Let's say I have a range called rng1

假设我有一个名为 rng1 的范围

Set rng1 = Worksheets("Sheet1").Range("A1","A5")

Is there a quick and easy way to perform a mathematical function, lets say divide all those cell values by 2, for all the cells in rng1?

对于 rng1 中的所有单元格，是否有一种快速简便的方法来执行数学函数，比如说将所有这些单元格值除以 2？

Any help is appreciated!

任何帮助表示赞赏！

回答by aevanko

It's very easy, but the final code will depend on where you want to store the new values. For example, if you want to store the values divided by 2 in the next column:

这很容易，但最终代码将取决于您想要存储新值的位置。例如，如果要将除以 2 的值存储在下一列中：

Sub test()

Dim cell As Range

For Each cell In Range("A1:A5")
    cell.Offset(, 1).Value = cell.Value / 2
Next

End Sub

Mind you there are more efficient ways to do this than using offset if your range is large, but for a smaller range, this is totally acceptable and fast.

请注意，如果您的范围很大，则有比使用偏移更有效的方法，但对于较小的范围，这是完全可以接受且快速的。

If you want to overwrite the values, you can simply use cell.Valuein place of cell.Offset(,1).Value

如果你想覆盖这些值，你可以简单地使用cell.Value代替cell.Offset(,1).Value

回答by Santosh

Another Way

其它的办法

Sub Main()
    [B1:B5] = [INDEX((A1:A5/2),)]
End Sub

How it works is well explained here.

这里很好地解释了它是如何工作的。