How do I obtain decimals instead of rounding?

P

Pookie76

I have a table in sheet 2 and am using a macro to convert a letter cod
into a digit. When the number is converted, it is rounded. Even when
increase the decimals, the numbers are still 0. How do I make it so
can obtain a full calculation without the rounding? Thanks
 
R

Ron Rosenfeld

I have a table in sheet 2 and am using a macro to convert a letter code
into a digit. When the number is converted, it is rounded. Even when I
increase the decimals, the numbers are still 0. How do I make it so I
can obtain a full calculation without the rounding? Thanks!
Click to expand...

It may only be the display that is being rounded. If so, you can format the
cell. Or it may be something in the macro.

Please post the macro that you are using, as well as the actual and expected
results, and some one will be able to help you more effectively.


--ron
 
P

Pookie76

Ron said:
*
Please post the macro that you are using, as well as the actual an
expected
results, and some one will be able to help you more effectively.


--ron *
Click to expand...

Hey Ron,
here is the macro that is being used:
Option Explicit
Function myConversionA(rng As Range) As Long
'returns a whole number???
' As Double
'if you have fractions

Dim res As Variant
Dim LookUpTable As Range
Dim iCtr As Long
Dim myValue As Long

Set rng = rng(1)
Set LookUpTable = Worksheets("sheet2").Range("a:c")

myValue = 0
For iCtr = 1 To Len(rng.Value)
res = Application.VLookup(Mid(rng.Value, iCtr, 1), _
LookUpTable, 3, False)

If IsError(res) Then
'do nothing
Else
If IsNumeric(res) Then
myValue = myValue + res
End If
End If

Next iCtr

myConversionA = myValue

End Function


I have a table of the alphabet and each letter has a specific numerica
value associated to it. Ie:
A=1.11, B=2.22, C=3.33 and so forth.

If I plug in A, my answer is 1. not 1.11
If I plug in ABC, my answer is 6. not 6.66.

Is there a way so I can get the decimals to be included in the code
Thanks
 
4

43N79W

Pookie76 > said:
Hey Ron,
here is the macro that is being used:
Option Explicit
Function myConversionA(rng As Range) As Long
'returns a whole number???
' As Double
'if you have fractions
Click to expand...

[snip]

From your code:
"Function myConversionA(rng As Range) As Long"

You've defined your function to return an integer. Try changing Long to
Double.

-gk-
 
R

Ron Rosenfeld

Hey Ron,
here is the macro that is being used:
Option Explicit ---------------------------------------------
Function myConversionA(rng As Range) As Long ----------------------------------------------
'returns a whole number???
' As Double
'if you have fractions

Dim res As Variant
Dim LookUpTable As Range
Dim iCtr As Long -------------------------------
Dim myValue As Long -------------------------------

Set rng = rng(1)
Set LookUpTable = Worksheets("sheet2").Range("a:c")

myValue = 0
For iCtr = 1 To Len(rng.Value)
res = Application.VLookup(Mid(rng.Value, iCtr, 1), _
LookUpTable, 3, False)

If IsError(res) Then
'do nothing
Else
If IsNumeric(res) Then
myValue = myValue + res
End If
End If

Next iCtr

myConversionA = myValue

End Function
Click to expand...

Pookie,

'Long' is an integer type so, since some of your variables, include your return
variable, are defined that way, VBA will round your result to a whole number.

It is OK to use Long for your counter, but try changing 'Long' to 'Double' in
the two lines I set off above.

I did not test this so let me know if it works.


--ron
 
Top