Excel VBA To Apply Formula To Each Cell Without Using Loop

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




data on which the formula is to be applied

Step 1

Open VB editor from Developer tab in excel as shown in the figure.

Open developer tab to write VBA to apply formula to a range without using loop

Step 2

Insert a module in which we will write a vba code to apply the formula to each cell without using loop.

Insert a module in the VB editor

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

vba code to apply the same formula to each cell without using loop

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.

Result showing formula is applied to each cell

Step 5

Let’s apply a formula to divide value in each cell by 10 without using loop in excel VBA.

apply division formula to each cell without using loop

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.

result showing formula is applied to the range

Hope this helped.




Share The Knowledge

Random Posts