efficent find all and value

[Guest]

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



--

 

[Guest]

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

 

[Guest]

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

 

[Tom Ogilvy]

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

 

[Guest]

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]

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

 

[Dave Peterson]

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>.