how can i find ' as the first characture in a cell using vba

[oletrol]

I am bringing code lines from a module over in to a worksheet and then
searching for a rem " ' " lines and removing them.
BUT once the line is in a cell the ' does not show in any find comand so I
can't find it to remove it. It shows in the formula bar still find don't find
it.

 

[Mike H]

Hi,

Right click your sheet tab. view code and paste this in and run it

Sub Kill_The_apostrophe()
For Each c In ActiveSheet.UsedRange
c.Formula = c.Formula
Next
End Sub

Mike

 

[Gene Mills]

Hi Mike and thanks before going any farther.

I have been working on this now for several days and still not right

You know that a ' as the first chr in a code line is like a rem chr.

But when the line of code is copied to cell the first ' like an identifier
to show the cell Text as viewed like('=2*2), it shows as =2*2 and without
the ' it appears as the answer to the formula.

I want to be able to find this first ' and then be able to delete the line
or change the ' to something else So I can then find the other ' later in
the code lines in the cells.

The macro you suggested ealier (below) works like a dream to just remove all
of the first ' but does Not allow me to check if first chr is a ' and do
this or do else

If I change this line to be c.Formula = chr(39) & c.Formula it will place an
* in every cell in the range Weather it has an ' or not because chr(39) &
c.formula is still * plus the existing blank formula.

I know of formula, text, an value and what they represent. But I don't know
if there are any other items things or names I could look for, or possibly
change some option that would allow a ' to show or Be seen by vba to do
something with the ' . Could I possibly turn of calculate and be able to do
what I want. ........

Any help would be deeply appreciated

Thanks Again

Gene