## 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...

read more
## Recent Comments