Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

NorthNone

Programmer
Jan 27, 2003
445
US
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
 
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
 
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
 
Why using this SpecialCells([!]xlConstants[/!]) ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top