Not so bad...

You could always say it was in its first printing. And then give them a
mistyped URL to find the corrections. <vbg.


Tom Ogilvy wrote:

Thanks for the sentiment, but
There would be too many typos and they would probably want Dave Peterson to
Review it. Yikes!

--
Regards,
Tom Ogilvy

"Ben" ? wrote in message
...
? 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
? ? ? ? ?
? ? ? ? ?
? ? ? ? ?
? ? ? ? ? --
? ? ? ? ?
? ?
? ?
? ?

--

Dave Peterson