Use VBA in Excel to find and replace selected formulas with values

[NorthNone]

I want to loop through all the cells in a workbook, find those with formulas that begin with
=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


"Character cannot be developed in ease and quiet. Only through experience of trial and suffering can the soul be strengthened, ambition inspired, and success achieved." - Helen Keller

 

[Fr33dan]

Well you'll need to use Cells.find, Check the built in Help start working on some code and come back if you need any help with it. We will not write the code for you

 

[NorthNone]

Thanks for your prompt response Fr33dan. I was trying to stay away from posting too much code, but I see that was a mistake. I can't get the
If Left(Cell.Formula, 5) = "=SUM(" Then
to ever pick a cell!!! Once I get that I think I'll have a good shot at replacing the formula with the value. Perhaps I should have limited my post to the one issue.
NorthNone

Sub FindFormula()
On Error Resume Next
Dim Cell As Range
Dim CellCount As Long
Dim i As Long

i = 0
If Selection.Rows.Count * Selection.Columns.Count > 1 Then
CellCount = Selection.Rows.Count * Selection.Columns.Count
Else
CellCount = ActiveSheet.UsedRange.Rows.Count * ActiveSheet.UsedRange.Columns.Count
End If

For Each Cell In Selection.SpecialCells(xlConstants)
If Left(Cell.Formula, 5) = "=SUM(" Then
With Cell.Interior
.ColorIndex = xlNone '.Debug.Print Left(Cell.Formula, 8)
.Pattern = xlPatternNone
End With
End If
Next Cell
End Sub


"Character cannot be developed in ease and quiet. Only through experience of trial and suffering can the soul be strengthened, ambition inspired, and success achieved." - Helen Keller

 

[PHV]

Why using this SpecialCells([!]xlConstants[/!]) ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[NorthNone]

Here's what works:
For Each cl In Cells.SpecialCells(xlCellTypeFormulas)
If Left(cl.Formula, 3) = "=HP" Then
cl = cl.Value
cl.Interior.Color = vbYellow
End If
Next
PHV: apparently I errer in using xlConstants. I should have used xlCellTypeFormula. Thanks for posting!
NorthNone

"Character cannot be developed in ease and quiet. Only through experience of trial and suffering can the soul be strengthened, ambition inspired, and success achieved." - Helen Keller