I have a macro that performs an edit on the the length of
cell. If it is more than 4 characters, it provides an
error message and stops the macro so the user can fix the
problem.

One user has entered data that is confusing. The cell is
displayed as '0599 on the toolbar. In the cell it looks
like 0599.

Another macro uses looks at the cell and takes the first 2
characters '5 and put it in a column. The macro then
takes the next 2 characters 09 and puts it in a second
column. This causes an error because we are looking for
05 and 99.

When I check the length of the variable with '0599, it is
4. I'n not sure how to work with this data. How do I
handle the tick mark (') in the first macro?

Any suggestions would be appreciated. Thanks for the help.

Entering a data into cell with preceeding " ' " casues it to be a text.
so '0599 will be treated as text 0599 and Len funtion will return 4.

In your macro when your refer to the cell value you can use Val.
e.g. : Instead of Len(Cells(1, 1).Value) use:
Len (Val(Cells(1, 1).Value)).
If you are assinging the cell value to a varible, declare the variable
as an intger
e.g. :
Dim myInt As Integer

myInt = Cells(1, 1).Value

Or if it is not delcared as integer again use Val function as under:

myInt = Val(Cells(1, 1).Value)

Sharad


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Why not just load a string with that value, and check if the first char is a
', if so replace it. Then just carry on as before.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"JT" wrote in message
...
I have a macro that performs an edit on the the length of
cell. If it is more than 4 characters, it provides an
error message and stops the macro so the user can fix the
problem.

One user has entered data that is confusing. The cell is
displayed as '0599 on the toolbar. In the cell it looks
like 0599.

Another macro uses looks at the cell and takes the first 2
characters '5 and put it in a column. The macro then
takes the next 2 characters 09 and puts it in a second
column. This causes an error because we are looking for
05 and 99.

When I check the length of the variable with '0599, it is
4. I'n not sure how to work with this data. How do I
handle the tick mark (') in the first macro?

Any suggestions would be appreciated. Thanks for the help.

I must be missing something because the variables do not
show the tick mark ('). The only place that I see the
tick mark is on the toolbar.

Some of the cells Im looking at are all numeric and some
are alpha numeric combinations.


-----Original Message-----
Why not just load a string with that value, and check if
the first char is a
', if so replace it. Then just carry on as before.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"JT" wrote in
message
...
I have a macro that performs an edit on the the length
of
cell. If it is more than 4 characters, it provides an
error message and stops the macro so the user can fix
the
problem.

One user has entered data that is confusing. The cell
is
displayed as '0599 on the toolbar. In the cell it looks
like 0599.

Another macro uses looks at the cell and takes the
first 2
characters '5 and put it in a column. The macro then
takes the next 2 characters 09 and puts it in a second
column. This causes an error because we are looking for
05 and 99.

When I check the length of the variable with '0599, it
is
4. I'n not sure how to work with this data. How do I
handle the tick mark (') in the first macro?

Any suggestions would be appreciated. Thanks for the
help.


.

Thanks for the help. I forgot to mention that the cells
I'm checking can be either numberic or alpha numberic.
I've tried the solution you have suggested without success.

For instance, '0599 could be '53ZZ. when I try the
solution below, I get a len of 2. I've tried a couple of
different variables but can never get it to show a len of
5. If I can get a len of 5 then I could manipulate the
variable.

Thanks for the help.

-----Original Message-----

Entering a data into cell with preceeding " ' " casues it
to be a text.
so '0599 will be treated as text 0599 and Len funtion
will return 4.

In your macro when your refer to the cell value you can
use Val.
e.g. : Instead of Len(Cells(1, 1).Value) use:
Len (Val(Cells(1, 1).Value)).
If you are assinging the cell value to a varible, declare
the variable
as an intger
e.g. :
Dim myInt As Integer

myInt = Cells(1, 1).Value

Or if it is not delcared as integer again use Val
function as under:

myInt = Val(Cells(1, 1).Value)

Sharad


*** Sent via Developersdex http://www.developersdex.com
***
Don't just participate in USENET...get rewarded for it!
.

Well JT,

This should work:-

In your code put this at the top -
Dim c
For Each c In Worksheets("Sheet_Name_Here).UsedRange.Cells
c.Value = Application.WorksheetFunction.Clean(c.Value)
Next c

This will remove the preceeding ' from all cell if it exists, from all
the cells. You can then work without bothering about ' .

In case cleaning all the cells will create a problem (it removes all
un-printable characters. First ' is trated as non printable, subsequent
ones aren't), before your len command, you can celan each specific
cell. e.g.:
Range("A5").Value =
Application.WorksheetFunction.Clean(Range("A5").Va lue)

..

Sharad

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!