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



--

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



--

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



--

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



--

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



--

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



--

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