Replace Excel Formulas with Values Through Code

I generated some values for an exercise program. The Excel worksheet thus had cells with formulas, while some cells had the figures used to arrive at the final results. I wanted to get rid of these intermediate figures, but that would mean the final results would get affected as well.
So I recorded a macro and then modified it and came up with the procedure below.
The comments explain what is happening at each step.

I hope you find it useful.

Sub ReplaceCellValueWithSelf()

‘ ReplaceCellValueWithSelf Macro
‘ Macro recorded 25-06-2014 by Abraham.


Dim intMyValue As Integer, i As Integer, j As Integer
For j = 1 To 80 ‘ Do this for 80 rows
For i = 1 To 5 ‘ do this for current column up to the 5th
intMyValue = ActiveCell.Value ‘Read the existing value (which could be the result of a formula)
ActiveCell viagra a 50 mg.Value = intMyValue ‘ Place the value in the same cell (replacing the formula)
ActiveCell.Offset(0, 1).Activate ‘ move to the (same row,) next column
Next i ‘restart procedure for the new column
ActiveCell.Offset(1, -5).Activate ‘ move one row down and to the 5th column to the left (i.e. back to starting column)
Next j ‘restart procedure for the new row

End Sub

Here is a modified version of the procedure that works down one column then goes to the next one.

Sub ReplaceCellValueWithSelfAcross()

‘ ReplaceCellValueWithSelfAcross Macro
‘ Macro recorded 25-06-2014 by Abraham.


Dim intMyValue As Integer, i As Integer, j As Integer
For j = 1 To 80 ‘ Do this for 80 columns
For i = 1 To 5 ‘ do this for current row up to the 5th
intMyValue = ActiveCell.Value ‘Read the existing value (which could be the result of a formula)
ActiveCell.Value = intMyValue ‘ Place the value in the same cell (replacing the formula)
ActiveCell.Offset(1, 0).Activate ‘ move to the next row, same column
Next i ‘restart procedure for the new row
ActiveCell.Offset(-5, 1).Activate ‘ move to the 5th row upwards (i.e. back to starting row) and one column to the right
Next j ‘restart procedure for the new column

End Sub

We look forward to reading your comment!

%d bloggers like this: