Excel VBA - Performing function to each cells in range

Question

Let's say I have a range called 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?

Any help is appreciated!

Answer 1

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:

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.Value in place of cell.Offset(,1).Value