I want to use the change event of the worksheet object, add code that
will take the numeric value entered in the cell, for instance, 55678
and translate the number to 556.78 automatically. I have code that
works, but I keep getting a type mismatch error when I convert the
number to a string and back again. If I don't convert, I get
something like this, 556..78. If I convert, i get a type mismatch,
however the code continues to work and I get 556.78, only I get a type
mismatch error.

Dim v1 as String
Dim v2 as String
Dim v3 as String

v1 = Right(target.value,2)
v2 = Left(target.value,Len(target.value)-2)
v3 = v2 & "." v1
target .value = v3

any suggestions are appreciated.

stan

If I'm understanding, you want a result that's the original number divided
by 100. If that's correct then this works:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Application.IsNumber(Target) Then
Application.EnableEvents = False
Target.Value = Target.Value / 100
Application.EnableEvents = True
End If

End Sub

hth,

Doug

wrote in message
om...
I want to use the change event of the worksheet object, add code that
will take the numeric value entered in the cell, for instance, 55678
and translate the number to 556.78 automatically. I have code that
works, but I keep getting a type mismatch error when I convert the
number to a string and back again. If I don't convert, I get
something like this, 556..78. If I convert, i get a type mismatch,
however the code continues to work and I get 556.78, only I get a type
mismatch error.

Dim v1 as String
Dim v2 as String
Dim v3 as String

v1 = Right(target.value,2)
v2 = Left(target.value,Len(target.value)-2)
v3 = v2 & "." v1
target .value = v3

any suggestions are appreciated.

stan

Why not just use:

Tools | Options | Edit | Fixed Decimal | Places = 2

?

--

Vasant

wrote in message
om...
I want to use the change event of the worksheet object, add code that
will take the numeric value entered in the cell, for instance, 55678
and translate the number to 556.78 automatically. I have code that
works, but I keep getting a type mismatch error when I convert the
number to a string and back again. If I don't convert, I get
something like this, 556..78. If I convert, i get a type mismatch,
however the code continues to work and I get 556.78, only I get a type
mismatch error.

Dim v1 as String
Dim v2 as String
Dim v3 as String

v1 = Right(target.value,2)
v2 = Left(target.value,Len(target.value)-2)
v3 = v2 & "." v1
target .value = v3

any suggestions are appreciated.

stan

Thank you for you help. I can't use the property in Tools Options
because only certain cells can be affected.

I did not even consider target.value / 100, i think that might work.

stan