Strange data type error
 

The following Function scans four tax tables (married, single etc) and
calculates the tax on the prize amount.
The functon works for all elements of the tax tables excpt one cell which
when read causes a data type error.
Placing the mousel pointer over each of the code piece for each cell shows a
number with an numeric data type, ie
Sheet2Cells(i,5)=665421. That is, all but Sheet2Cells(i,4)="1064.12" which
is a string data type. An indipendent check shows the 1064.12 to be numeric.
I can't figure this one out

Thanks for any help on this.


Function TaxIt(Prize As Currency, Status As Integer) As Currency
For i = Status To Status + 5
If i = (Status + 5) Or (Prize Sheet2.Cells(i, 2) And _
Prize < Sheet2.Cells(i, 3)) Then
TaxIt = Sheet2.Cells(i, 4) + Sheet2.Cells(i, 5) * (Prize -
Sheet2.Cells(i, 2))
Exit For
End If
Next
End Function

Strange data type error
 

Try re-entering 1064.12 in the cell in question. Check that it aligns
to the right of the cell (assuming there is no other alignment set).

The cell may have been formatted as text and then changed to a number
format. In my experience, numbers entered in such cells remain as text
until re-entered.

Strange data type error
 

Mark yourare on to something. T he cell is the only one aligned to the
right. I tried to re-enter the number, align it to the left but nothing
changed. I also deleted the cell, copied the whole table to a new location,
entered another number, etc. None corredted the proboem. Do you have any
other ideas? Microsoft does know about this problem don't they? I suppose I
will have to retype the whole table in a new location. Don't much like that
idea.

Thanks

"Mark Lincoln" wrote:

Try re-entering 1064.12 in the cell in question. Check that it align
to the right of the cell (assuming there is no other alignment set).

The cell may have been formatted as text and then changed to a number
format. In my experience, numbers entered in such cells remain as text
until re-entered.

Strange data type error
 

Numbers should align to the right in the absence of overriding
formatting. Text aligns to the left unless the cell format aligns it
differently. So if that cell is the only one right-aligned, I'm a bit
confused.

If everything else works in column D, then I don't think this is an
Excel problem that Microsoft needs to fix. There must be something
wrong with the particular cell in which you have that number. Let's
see what we can try:

What is the value of i when the code fails? Is it always the same? If
so, then cell Di (where i is the row) must be formatted incorrectly or
the number in it must be entered incorrectly. If not, then you
actually have a problem with more than one cell in that column. Or
with the macro code.

Assuming there is a problem with only one cell, try filling it in with
the contents of the cell above it by dragging (as if you were copying a
formula). This should put a number into the cell and overwrite any
problem formatting with a format that works. Alternatively, you could
copy a known working value from the same column and paste it into the
problem cell. Then change the value of the cell back to 1064.12 and
see if your code fails again.

If you still can't find the problem I could have you email the workbook
to me and attempt to find the problem directly. I hope you'll find it
first, though. Good luck, and let us know what you find out!

Strange data type error
 

I gave the program one last try before rewriting the in a new location. It
worked.
I don' t know what I did to make it work since I tried a number of things.
I did copy the tables to a new location about 35 rows lower down in Sheet2
and changed the parameter "status" and the code failed at the same relative
location 35 lrower down. Which would indicate that somthing in the cell
properties is at fault. Since the program started working I have not been
able to duplicate the problem.

Thanks for taking the time to work on this problem.

"Mark Lincoln" wrote:

Numbers should align to the right in the absence of overriding
formatting. Text aligns to the left unless the cell format aligns it
differently. So if that cell is the only one right-aligned, I'm a bit
confused.

If everything else works in column D, then I don't think this is an
Excel problem that Microsoft needs to fix. There must be something
wrong with the particular cell in which you have that number. Let's
see what we can try:

What is the value of i when the code fails? Is it always the same? If
so, then cell Di (where i is the row) must be formatted incorrectly or
the number in it must be entered incorrectly. If not, then you
actually have a problem with more than one cell in that column. Or
with the macro code.

Assuming there is a problem with only one cell, try filling it in with
the contents of the cell above it by dragging (as if you were copying a
formula). This should put a number into the cell and overwrite any
problem formatting with a format that works. Alternatively, you could
copy a known working value from the same column and paste it into the
problem cell. Then change the value of the cell back to 1064.12 and
see if your code fails again.

If you still can't find the problem I could have you email the workbook
to me and attempt to find the problem directly. I hope you'll find it
first, though. Good luck, and let us know what you find out!

Strange data type error
 

Hey, as long as it works. That's the important thing. Glad to see it.

Besides, I've been known to fix problems with no idea as to how I did
it. :-)

Strange data type error
 

I wonder if a copy paste-special 'values only' to a newly inserted
sheet would remove the problem formatting?

Strange data type error
 

Good Idea. Unfortunly I have not been able to repeat the problem.

John



"Buck" wrote:

I wonder if a copy paste-special 'values only' to a newly inserted
sheet would remove the problem formatting?