We are going to present an alternative to the
standard copy and paste
values to a selected range.
This is what we have done here using the
standard copy, paste values
option, which is native to Excel.
We will write an alternative macro solution,
which will enable a seamless conversion of
formulas to text in a selected range.
Go to Visual Basic for Applications
and insert a Module.
Please remember to narrate the macro.
Don't forget to add the Option Explicit syntax,
because it forces you to declare all of the
variables in a specific macro such as this one.
We will call this macro, rangeFormulasValues
First, we will disable the Excel properties
before we run the formulas to values macro.
The one object variable for this macro is
selectedCells, which needs to be declared.
Next we need to add the loop code, which will
loop through each cell in the selected range.
It is prudent to add an error handler in the
macro code, which will help
to minimize run-time errors.
To convert each cell's formula to the value
itself, we can simply use this syntax.
If the cell in question is nothing,
then we can move to the next cell, hence this
code will move the macro to the next cell.
Otherwise, the macro formats on the underlying
cell, and we can now move to the next cell.
The macro is complete, we can now
re-enable the three Excel properties,
which we initially disabled.
We can now review the macro
before we road-test the macro.
Let's run the macro.
As you can see, the macro is running correctly.
We will test the macro one final time.
Don't hesitate to send us a comment,
like this video or subscribe to our channel.