If (starts with ', then delete the ')
 

Gentlemen (and Ladies),

Unfortunately the last person who did this job entered dates with a "'"
in front of all of them, and excel will not recognize them as dates.
I'm looking for some code that will do the following:

rownum = 7

Do
if Range("P" & rownum).Value [begins with "'"] then
[delete the "'"]
rownum = rownum + 1
Does anybody know how to do something like this?

If (starts with ', then delete the ')
 

one way?
Sub nomoreapos()
For Each c In Selection
c.Value = Right(c, Len(c) - 1)
Next c

--
Don Guillett
SalesAid Software

wrote in message
oups.com...
Gentlemen (and Ladies),

Unfortunately the last person who did this job entered dates with a "'"
in front of all of them, and excel will not recognize them as dates.
I'm looking for some code that will do the following:

rownum = 7

Do
if Range("P" & rownum).Value [begins with "'"] then
[delete the "'"]
rownum = rownum + 1
Does anybody know how to do something like this?

If (starts with ', then delete the ')
 

Can you elaborate? What does Len do? What does Right do?

Don Guillett wrote:
one way?
Sub nomoreapos()
For Each c In Selection
c.Value = Right(c, Len(c) - 1)
Next c

--
Don Guillett
SalesAid Software

wrote in message
oups.com...
Gentlemen (and Ladies),

Unfortunately the last person who did this job entered dates with a
"'"
in front of all of them, and excel will not recognize them as
dates.
I'm looking for some code that will do the following:

rownum = 7

Do
if Range("P" & rownum).Value [begins with "'"] then
[delete the "'"]
rownum = rownum + 1
Does anybody know how to do something like this?

If (starts with ', then delete the ')
 

The line

c.Value=(Right(c,Len(c)-1)

Can be interpreted as follows:

First we determine the value in cell c, for example '12/31/04
Then working inside out in the parenthesis, we take the length (Len) of the
value in the cell (in this case, 9) then subtract 1 (result = 8).
We next extract from the value of cell c, 8 characters from right to left,
resulting in 12/31/04 (without the ').
We then replace the value of cell c ('12/31/04) with those 8 characters
(12/31/04).

Len determines the length of the value and Right pulls "x" number of
characters going from left to right as in
Right(string, 1)
returns the rightmost charcter.

There are more elaborate explanations in the Excel VBA help.

HTH

wrote in message
oups.com...
Can you elaborate? What does Len do? What does Right do?

Don Guillett wrote:
one way?
Sub nomoreapos()
For Each c In Selection
c.Value = Right(c, Len(c) - 1)
Next c

--
Don Guillett
SalesAid Software

wrote in message
oups.com...
Gentlemen (and Ladies),

Unfortunately the last person who did this job entered dates with a
"'"
in front of all of them, and excel will not recognize them as
dates.
I'm looking for some code that will do the following:

rownum = 7

Do
if Range("P" & rownum).Value [begins with "'"] then
[delete the "'"]
rownum = rownum + 1
Does anybody know how to do something like this?

If (starts with ', then delete the ')
 

Hi
in the VBA editor select these functions and press F1

--
Regards
Frank Kabel
Frankfurt, Germany
schrieb im Newsbeitrag
oups.com...
Can you elaborate? What does Len do? What does Right do?

Don Guillett wrote:
one way?
Sub nomoreapos()
For Each c In Selection
c.Value = Right(c, Len(c) - 1)
Next c

--
Don Guillett
SalesAid Software

wrote in message
oups.com...
Gentlemen (and Ladies),

Unfortunately the last person who did this job entered dates with a
"'"
in front of all of them, and excel will not recognize them as
dates.
I'm looking for some code that will do the following:

rownum = 7

Do
if Range("P" & rownum).Value [begins with "'"] then
[delete the "'"]
rownum = rownum + 1
Does anybody know how to do something like this?

If (starts with ', then delete the ')
 

This is not working. Len is ignoring the apostrophe when determining
the length so this function is deleting the first number in the cell.
Any other ideas? Thank you for your help.

Don Guillett wrote:
one way?
Sub nomoreapos()
For Each c In Selection
c.Value = Right(c, Len(c) - 1)
Next c

--
Don Guillett
SalesAid Software

wrote in message
oups.com...
Gentlemen (and Ladies),

Unfortunately the last person who did this job entered dates with a
"'"
in front of all of them, and excel will not recognize them as
dates.
I'm looking for some code that will do the following:

rownum = 7

Do
if Range("P" & rownum).Value [begins with "'"] then
[delete the "'"]
rownum = rownum + 1
Does anybody know how to do something like this?

If (starts with ', then delete the ')
 

Apparently replacing the cell value with itself elimiates the apostrophe:

Sub nomoreapos()

For Each c In Selection
c.Value = c.Value
Next c

End Sub

wrote in message
oups.com...
This is not working. Len is ignoring the apostrophe when determining
the length so this function is deleting the first number in the cell.
Any other ideas? Thank you for your help.

Don Guillett wrote:
one way?
Sub nomoreapos()
For Each c In Selection
c.Value = Right(c, Len(c) - 1)
Next c

--
Don Guillett
SalesAid Software

wrote in message
oups.com...
Gentlemen (and Ladies),

Unfortunately the last person who did this job entered dates with a
"'"
in front of all of them, and excel will not recognize them as
dates.
I'm looking for some code that will do the following:

rownum = 7

Do
if Range("P" & rownum).Value [begins with "'"] then
[delete the "'"]
rownum = rownum + 1
Does anybody know how to do something like this?

If (starts with ', then delete the ')
 

Hi Michael McClellan,

One thing to be aware of - replacing all cells with their values will remove
any formulas you have. So you may want to do something like this:

Sub FixDates()
Dim c As Range

For Each c In ActiveSheet.UsedRange
If Not c.HasFormula And IsDate(c.Value) Then _
c.Value = c.Value
Next c
End Sub

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


Michael Malinsky wrote:
Apparently replacing the cell value with itself elimiates the
apostrophe:

Sub nomoreapos()

For Each c In Selection
c.Value = c.Value
Next c

End Sub

wrote in message
oups.com...
This is not working. Len is ignoring the apostrophe when determining
the length so this function is deleting the first number in the cell.
Any other ideas? Thank you for your help.

Don Guillett wrote:
one way?
Sub nomoreapos()
For Each c In Selection
c.Value = Right(c, Len(c) - 1)
Next c

--
Don Guillett
SalesAid Software

wrote in message
oups.com...
Gentlemen (and Ladies),

Unfortunately the last person who did this job entered dates with
a "'" in front of all of them, and excel will not recognize them
as dates. I'm looking for some code that will do the following:

rownum = 7

Do
if Range("P" & rownum).Value [begins with "'"] then
[delete the "'"]
rownum = rownum + 1
Does anybody know how to do something like this?

If (starts with ', then delete the ')
 

You're right. Use the .value method

--
Don Guillett
SalesAid Software

wrote in message
oups.com...
This is not working. Len is ignoring the apostrophe when determining
the length so this function is deleting the first number in the cell.
Any other ideas? Thank you for your help.

Don Guillett wrote:
one way?
Sub nomoreapos()
For Each c In Selection
c.Value = Right(c, Len(c) - 1)
Next c

--
Don Guillett
SalesAid Software

wrote in message
oups.com...
Gentlemen (and Ladies),

Unfortunately the last person who did this job entered dates with a
"'"
in front of all of them, and excel will not recognize them as
dates.
I'm looking for some code that will do the following:

rownum = 7

Do
if Range("P" & rownum).Value [begins with "'"] then
[delete the "'"]
rownum = rownum + 1
Does anybody know how to do something like this?

If (starts with ', then delete the ')
 

Easier than mucking about in VBA:

Copy a blank cell
Select the cells which have an apostrophe as the first character (which is not
recognized by VBA or Excel)
Edit menu Paste Special Values and Operation - Add options

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

wrote:

Gentlemen (and Ladies),

Unfortunately the last person who did this job entered dates with a "'"
in front of all of them, and excel will not recognize them as dates.
I'm looking for some code that will do the following:

rownum = 7

Do
if Range("P" & rownum).Value [begins with "'"] then
[delete the "'"]
rownum = rownum + 1
Does anybody know how to do something like this?