Selecting all formulae with a given reference and pasting contents as values

Posted by Graham Shaw on November 03, 2000 5:55 AM

I would like to search for all cells in my worksheet that have a formula containing a certain string. I then want to convert all these selected cells to be a value rather than a formula.

Posted by Tim Francis-Wright on November 03, 2000 12:41 PM

This should do the trick:
Sub Macro1()
Dim cel As Object
Dim Message$, Title$, Default$, Answer$
Message = "Replace formulas containing this string with their values:"
Title = "I got help from mrexcel.com"
Default = "SUM("
Answer = InputBox(Message, Title, Default)
For Each cel In ActiveSheet.UsedRange
If cel.Formula Like "*" & Answer & "*" And Not Application.WorksheetFunction.IsText(cel) Then
cel.Copy
cel.PasteSpecial Paste:=xlValues
End If
Next
End Sub

HTH!

Posted by Graham Shaw on November 06, 2000 12:37 AM

Tim,

Thanks for your time. I appreciate it. Cheers