In this post we will see how to apply a formula to an entire range I excel without using loop.
As good as loops are but they leverage considerable resources while executing the program, your program may run slow as looping involves traversing each of the cells and executing the statements, on the other hand it also occupies a lot of memory in RAM.
We will see how to apply a formula or an operation to entire range without actually using loop in excel VBA.
Below is the data range and we would like to apply UPCASE formula to each of the cells in this range without using loop, we have names of continents in the range A1:A5
Step 1
Open VB editor from Developer tab in excel as shown in the figure.
Step 2
Insert a module in which we will write a vba code to apply the formula to each cell without using loop.
Step 3
Now double click the newly created module to open that and paste the below code in the module.
Sub Sample()
[A1:A10] = [INDEX(UPPER(A1:A10),)]
End Sub
Index and upper are worksheet functions and we are asking VBA to return an array and that’s why we have used INDEX function to return the result, so basically VBA is applying the formula to each cell without using loop and is returning the result in an array and the changes are reflected in the data in Sheet1
Run the program by pressing F5 or play button in the VB editor and you will see the result where each cell value is converted to upper case.
Step 5
Let’s apply a formula to divide value in each cell by 10 without using loop in excel VBA.
Paste the code below to VB editor.
Sub Sample()
[A1:A5] = [INDEX((A1:A5)/10,)]
End Sub
As you can see in the result below we have applied the division formula to each of the cell without using the loop in excel vba.
Hope this helped.
