Excel VBA - executing a function for each cell in a 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, for example, divide all these cell values ​​by 2, for all cells in rng1?

Any help is appreciated!

Answer 1

It is very simple, but the final code will depend on where you want to save the new values. For example, if you want to keep the values ​​divided by 2 in the following column:

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

Remember that there are more effective ways to do this than using offset if your range is large, but for a smaller range this is acceptable and fast.

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

Answer 2

Another way

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

How this works is well explained here.