UtterAccess Discussion Forums > Use VBA to find and replace selected formulas with values

Full Version: Use VBA to find and replace selected formulas with values

UtterAccess Discussion Forums > Microsoft® Office > Microsoft Excel

NorthNone

Jul 30 2007, 02:47 PM

I want to loop through all the cells in a workbook, find those with formulas that begin with

CODE

=sum(

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?
TIA
NorthNone

Luceze

Jul 30 2007, 04:28 PM

Hiya NorthNone,

This should do the trick.

CODE

Sub ConvertSums()
Dim cl As Range
    For Each cl In Cells.SpecialCells(xlCellTypeFormulas)
        If Left(cl.Formula, 4) = "=SUM" Then
            cl = cl.Value
        End If
    Next
End Sub

This bit of code relies on the activesheet so adjust as you see necessary.

HTH,

NorthNone

Jul 31 2007, 07:47 AM

Thanks, Eric. That did the trick!
Have a great day.
NorthNone

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.