Formula to Text to Formula

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!

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


"Bonnie" wrote:

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!

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'!


"Bonnie" wrote:

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!

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.



"bj" wrote:

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'!


"Bonnie" wrote:

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!

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

"Duke Carey" wrote:

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.



"bj" wrote:

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'!


"Bonnie" wrote:

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!

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

Bonnie wrote:

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!

--

Dave Peterson

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.

-----Original Message-----
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

Bonnie wrote:

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!

--

Dave Peterson
.

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.

-----Original Message-----
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
???

"Duke Carey" wrote:

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.



"bj" wrote:

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'!


"Bonnie" wrote:

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!

.

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!

-----Original Message-----
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


"Bonnie" wrote:

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!

.

David McRitchie has some notes at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Bonnie wrote:

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.

-----Original Message-----
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

Bonnie wrote:

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!

--

Dave Peterson
.


--

Dave Peterson