Formula to Text to Formula

B

Bonnie

Hi there! Using E02 on XP. Have zillions of formulas to
create and if I can get my template going, it will be much
easier. Only problem is this: I used Replace to change all
my ='1'!'s with '=1'1! so I can see my formulas and use
copy/replace to create my boiler templates on multiple
worksheets. I have done this in the past and then just
replace in reverse and voila! I have formulas linked to
the correct worksheet locations. This time I keep getting
the Excel can't find anything to replace, check your
formating, etc.

What am I doing wrong? I really do not want to key in 10
sheets of formulas.

Here is what my cell reads: '='1'!M68
My formula would be: ='1'!M68

Is it because I have used a number for the worksheet name?
I've played with it but can't fix it. Tried reformating
the cells, etc.

Would really appreciate any help or advice on this before
I wear my fingers down to nubs and get sheetsick. Thanks a
bunch for your time!
 
D

Duke Carey

I did a very quick test and this VBA code seemed to deal with what's ailing
you. Make sure you work on a copy of your file this time, ok!

Sub DeleteApostrophes()
Dim cc As range
Dim str As String

For Each cc In selection
str = cc.Formula
If Len(str) > 0 Then
If cc.Value = str And Left(str, 1) = "=" Then
cc.Formula = str
End If
End If
Next
End Sub

Good luck
 
B

bj

I am also using E02 on XP
I copied your equation into my computer
did find and replace and had no problems

does find and replace work correctly on other things for you?

are you really using the '=1'1! or just '=1'!
 
D

Duke Carey

bj -

I'm running E02 on Win2K and I've never been able to get rid of the leading
apostrophe using Find & Replace. Like the OP, I get a message that no
matching data could be found.

Wonder what's different in your setup than in mine.
 
B

bj

I redid it to verify
if I am on the cell containing the text just having pasted the text, it does
replace it
If I am not it does not find it and will not replace it
if I move back to it, it will not replace it
???
 
D

Dave Peterson

You could make your life a lot easier if you changed your formulas to text by
using a different string.

I use this.

Edit|replace
what: = (equal sign)
with: $$$$$= (as long as $$$$$ was not used anywhere in the worksheet)
replace all.

Then copy|paste and do the reverse:

edit|replace
what: $$$$$=
with: = (equal sign)
replace all.

Is it too late to go back and do it this way?

Or do you need to have a macro that corrects your current problem?

Maybe something like:

Option Explicit
Sub testme()

Dim myCell As Range
Dim myRng As Range

Set myRng = Nothing
On Error Resume Next
Set myRng = ActiveSheet.UsedRange.Cells _
.SpecialCells(xlCellTypeConstants, xlTextValues)
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "no constants!"
Exit Sub
End If

For Each myCell In myRng.Cells
myCell.Value = myCell.Value
Next myCell

End Sub
 
B

Bonnie

Thanks Dave. You are correct. I made things tougher on
myself by using the 'make it text' apostrophe. I'm so glad
I tested this before I got the entire first template done.
I don't work in Excel often and mostly just as a data dump.

I'd love to run your code but I'm an Access person and new
to Excel. Not sure where to put code and how to run it on
a spreadsheet. Used to forms, macros, buttons and event
procedures running things. Can you illuminate?

Thanks VERY much. Loved all the responses on this.
 
B

Bonnie

Yes, BJ, it does work if you are on just one cell. I just
won't work on a selected area. VERY odd. I should have
used a text string rather than the text identifier. Dang!
Thanks very much for the input.
 
B

Bonnie

Hi Mr. Carey! Thanks for the input on this conundrum. I
did work on a copy of my original master file but the
master file has NO formulas on it yet and the copy has the
first template almost done but with this problem. Would
love to run your code but usually work in Access and don't
know where/how to run code in Excel. Just never done it.

Thanks again for your participation in the newsgroups
helping folks out! LUV U GUYS!
 
R

RagDyeR

For future reference Bonnie,

Simply opening and closing TTC will eliminate the apostrophes.

Select the data (in a column),
<Data> <TextToColumns> <Finish>
And you're done!
 
R

RagDyer

Glad to help.

Appreciate the feed-back.
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

I LUV U, MAN!

Thanks BUNCHES for the easy info, I was getting ready to
try the code.

B
 
Top