Find and Replace error

E

Emece

Hi.

I have a worksheet where I want to show only some of the formulas used in
it. That is why, instead of using the option "Show formulas" from the
formulas Tab, I insert a ' before the = in the cells I want to see the
formula.

But when I want to show results once again, in order no to look for each
formula in which I added the ', I use the Find and Replace Option. In Find I
specify '= and leave in blank the Replace option. But Excel shows me the
following message: "Excel cannot find the data you are searching for".

Cannot figure out the correct way of doing it...

Thanks in advance for your help

Regards,
Emece.-
 
T

Tom-S

Try using " instead of '.

Alternatively, you switch between making formulas visible and formula
results visible by using Ctrl + `

Regards,

Tom
 
J

Jim Cone

See the following for the bad news...
"...Can't Use Find/Replace on Text with Leading Apostrophe"
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q214285

You could use a space followed by the apostrophe to display formulas.
Replace works with those two characters in that order.
Also, a properly written macro can clean up formulas with a leading apostrophe.
--
Jim Cone
Portland, Oregon USA
http://tinyurl.com/ExtrasForXL
(yes it cleans)






"Emece" <[email protected]>
wrote in messageHi.
I have a worksheet where I want to show only some of the formulas used in
it. That is why, instead of using the option "Show formulas" from the
formulas Tab, I insert a ' before the = in the cells I want to see the
formula.

But when I want to show results once again, in order no to look for each
formula in which I added the ', I use the Find and Replace Option. In Find I
specify '= and leave in blank the Replace option. But Excel shows me the
following message: "Excel cannot find the data you are searching for".

Cannot figure out the correct way of doing it...
Thanks in advance for your help
Regards,
Emece.-
 
G

Gord Dibben

To add a space at left of selected cells thus showing formulas.

Sub Add_Space()
Dim R As Range
Dim thisrng As Range
Set thisrng = Range(ActiveCell.Address & "," & Selection.Address)
For Each R In thisrng
R.Formula = " " & R.Formula
Next
End Sub

To remove the spaces.

Sub trim_spaces()
Dim R As Range
For Each R In Selection
If Not R.HasFormula Then
a = R.Address
R.Value = Evaluate("Trim(" & a & ")")
End If
Next
End Sub


Gord Dibben MS Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top