Use VBA to find and replace selected formulas with values
UtterAccess Discussion Forums
Jul 30 2007, 02:47 PM
I want to loop through all the cells in a workbook, find those with formulas that begin with
and replace them with the value of the cell. I do NOT want to replace all cells that have formulas with values, just the ones that sum a range of cells.
Can anyone help?
Jul 30 2007, 04:28 PM
This should do the trick.
Dim cl As Range
For Each cl In Cells.SpecialCells(xlCellTypeFormulas)
If Left(cl.Formula, 4) = "=SUM" Then
cl = cl.Value
This bit of code relies on the activesheet so adjust as you see necessary.
Jul 31 2007, 07:47 AM
Thanks, Eric. That did the trick!
Have a great day.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please