I have the following code.. It's producing a Runtime error 6 overflow.
I can't get it to produce it every time but generally it will fail when
entering about 10 values in the F:12 or cells. Also sometimes after
selecting a cell and hitting hte delete key it will force it to 0.00
and will stay that way until a new value is entered. Any ideas?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim i As Integer
Dim d As Double

Application.EnableEvents = False
With Target(1)

' Make sure Debits are postive values
If Not Intersect(.Cells, Range("F12",
Range("F12").End(xlDown))) Is Nothing Then
If IsNumeric(.Value) = True Then
.Value = Abs(.Value)
i = CInt(CDbl(.Value) * 100) ' This line is where the
error is reported from
d = CDbl(.Value) * 100
If Abs(d - i) 0 Then
MsgBox "Rounding error detected! Make sure you
don't have numbers with values less than 1/100th", vbExclamation
End If
Else
.Value = ""
End If
End If
End With
Application.EnableEvents = True
End Sub

One other thing to note.. The code is designed to take all non numbers
and just empty the contents of the cell but that doesn't seem to
function either..

The If IsNumeric(.Value) = True doesn't ever seem to be false

Hi

Integer can't keep more than 32k before going mad. Dim i as something
bigger, like Long, and see if it helps.

HTH. Best wishes Harald

Dim i As Integer
Dim d As Double
Integer can be up to +/- 32,767
Double can be +/- 2,147,483,647

If Value is larger than 327.68 then
i = CInt(CDbl(.Value) * 100)
will cause a numeric overflow. CInt can't return a value that large and i
can't contain it.

HTH,
--
George Nicholson

Remove 'Junk' from return address.


"Wescotte" wrote in message
oups.com...
I have the following code.. It's producing a Runtime error 6 overflow.
I can't get it to produce it every time but generally it will fail when
entering about 10 values in the F:12 or cells. Also sometimes after
selecting a cell and hitting hte delete key it will force it to 0.00
and will stay that way until a new value is entered. Any ideas?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim i As Integer
Dim d As Double

Application.EnableEvents = False
With Target(1)

' Make sure Debits are postive values
If Not Intersect(.Cells, Range("F12",
Range("F12").End(xlDown))) Is Nothing Then
If IsNumeric(.Value) = True Then
.Value = Abs(.Value)
i = CInt(CDbl(.Value) * 100) ' This line is where the
error is reported from
d = CDbl(.Value) * 100
If Abs(d - i) 0 Then
MsgBox "Rounding error detected! Make sure you
don't have numbers with values less than 1/100th", vbExclamation
End If
Else
.Value = ""
End If
End If
End With
Application.EnableEvents = True
End Sub

Wow, I remeber back in the old days coding with GW Basic int's only
being 2 bytes but I just assumed VB was 4. Is a long 4 bytes?

eek!!

Double can be +/- 2,147,483,647
should have read
Long can be +/- 2,147,483,647
(Double can pretty much be whatever it wants to be.)
Very sorry.

Byte is 1 Byte (d'oh)
Integer is 2 byte
Long is 4 byte

Single is 4 byte
Double is 8 byte

HTH,
--
George Nicholson

Remove 'Junk' from return address.



"Wescotte" wrote in message
ups.com...
Wow, I remeber back in the old days coding with GW Basic int's only
being 2 bytes but I just assumed VB was 4. Is a long 4 bytes?