Sub Test()
Dim SearchString, SearchSubstr, MyPos
Worksheets("Receipts-Initial").Activate
SearchSubstr = "SUMPRODUCT"
For Each Cell In Worksheets("Receipts-Initial").Range("A16:Q16")
SearchString = ActiveCell.Formula
MyPos = InStr(1, SearchString, SearchSubstr, vbTextCompare)
If MyPos < 0 Then
ActiveCell.Formula = ActiveCell.Value
End If
Next
End Sub

--
Regards,
Tom Ogilvy

"Carroll" wrote in message
oups.com...
Hello,

In testing, I tried the formula below without success. Basically, I
was trying to select only those cells within a range that contained the
function SUMPRODUCT. After selecting all instances where this is true,
I want to copy/paste the actual value of the result of that formula
over top of the formula. I have other formulas in my spreadsheet that
do not use SUMPRODUCT, but I don't want to touch those.

It appears that ActiveCell.Value is the value that the function
returns, not the formula itself, so I know that this doesn't help me,
but I couldn't find another property for ActiveCell that would make it
work.

Ultimately, I want to include multiple sheets in my search, so if you
know code where I can do it all in one fell swoop, that would be great.

Any ideas out there?

Sub Test()
Dim SearchString, SearchSubstr, MyPos
Worksheets("Receipts-Initial").Activate
SearchSubstr = "SUMPRODUCT"
For Each Cell In Worksheets("Receipts-Initial").Range("A16:Q16")
SearchString = ActiveCell.Value
MyPos = InStr(1, SearchString, SearchSubstr, 1)
If MyPos < 0 Then
ActiveCell.Select
End If
Next
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
End Sub

Thanks,
Carroll Rinehart