Thanks, Tom. You should DEFINITELY consider putting your thoughts and
knowledge about Excel into a book. It would definitely be on my bookshelf,
without doubt.

Ben
--



"Tom Ogilvy" wrote:

Dim rng as Range, cell as Range
set rng = cells.SpecialCells(xlFormulas)
for each cell in rng
if instr(1,cell.Formula,"xyz",vbTextCompare) then
cell.Formula = cell.Value
end if
Next

--
Regards,
Tom Ogilvy

"Ben" wrote in message
...
Jim,

Thanks for your response. I actually want to replace the formula that
begins with "xyz" with the actual value. That is, when I find such a cell
with the formula, I want to do a copy and paste value to it. Thanks for
your
help.

Ben


--



"Jim Thomlinson" wrote:

Your description is a little hard to follow but here is what I come up
with.
This sub looks through all of the formulas on all of the sheets in the
activeworkbook, replacing "xyz" with "zxy"...

Sub ReplaceStuff()
Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets
wks.Cells.Replace What:="xyz", Replacement:="zxy",
LookAt:=xlFormulas
Next wks

End Sub
--
HTH...

Jim Thomlinson


"Ben" wrote:

Hi all,

I have a 12MB spreadsheet, in it, it contains many formula scattered
all
over each worksheet tab. I need to find a very quick and efficient
way to
replace formulas that begins with say, "xyz" and value it.

There are about 15 tabs per workbook and each tab has about 9000
locations/
formulas all over the worksheet that I need to value, and there are
about 50
such workbooks every other day.

When I did a find all, in the find dialogue box, it lists all the
locations,
I guess, it would be ideal if my code can somehow capture that list of
cell
locations and just run throught that list for each tab and value those
cells
and move onto next worksheet and then after one workbook is done I can
move
to the next workbook and so on, until all workbooks are done.

If there are other efficient ways, I would be open to them as well.
Please
share with me your thoughts and code snippets. Thank so much for your
help.

Ben



--