Thread: function to change metric measurements

Hi

i requre a little bit of guidance and help to right a simple function

I have a value in milimeters which i want to pass to a function followed by
a string or value to show that i want the result converting in to

a) meters & cm - lets call it m
b) cms & mm - lets call it cm
c) mm - lets call it mm

even though i passing the mm's to the function as a number i wish it to
display it as a string

thus 1234,m i wish to return as 1 m 23.4 cm
or 1234,cm will return 123 cm 4 mms or 123.4 cms
or 1234 mm will return 1234 mms


I know this should be easy but i just cant seem how i should structure my
login..

was trying to use mod and fix .. i cant see why i can get my head round it..
as i done something similar for inches and passing the factor of the inches
.... ie 1/8th 1/16th etc to it.. to display feet inches and denominator...

please help..

A1=1234

in b1:

=convm(a1,"m")


Function ConvM(ByVal metric As Long, munits As String) As String
Select Case munits
Case Is = "m"
cm = (metric Mod 1000) / 10
m = Int(metric / 1000)
ConvM = Str(m) & " m" & Str(cm) & " cm"
Case Is = "cm"
cm = metric / 10
ConvM = Str(cm) & " cm"
Case Is = "mm"
mm = metric
ConvM = Str(mm) & " mm"
Case Else
ConvM = "Invalid Units"
End Select

End Function

"devo" wrote:

> Hi
>
> i requre a little bit of guidance and help to right a simple function
>
> I have a value in milimeters which i want to pass to a function followed by
> a string or value to show that i want the result converting in to
>
> a) meters & cm - lets call it m
> b) cms & mm - lets call it cm
> c) mm - lets call it mm
>
> even though i passing the mm's to the function as a number i wish it to
> display it as a string
>
> thus 1234,m i wish to return as 1 m 23.4 cm
> or 1234,cm will return 123 cm 4 mms or 123.4 cms
> or 1234 mm will return 1234 mms
>
>
> I know this should be easy but i just cant seem how i should structure my
> login..
>
> was trying to use mod and fix .. i cant see why i can get my head round it..
> as i done something similar for inches and passing the factor of the inches
> .... ie 1/8th 1/16th etc to it.. to display feet inches and denominator...
>
> please help..
>
>
>

The base units are mm to be converted to m, cm or left as mm.

So we always start with 1234 mm =1.234 m or 1m 23.4 cm = 123 cm 4mm (123.4 cm)

"Bearacade" wrote:

>
> I am not sure you are giving us the right example of what you want..
>
> 1234m does not equal to 1 M 23.4 CM
>
> nor does 1234cm = 123 cm 4 mm and vise versa..
>
> I did take what I think you meant and put this into a formula (it's not
> pretty, but it works) and you don't have to set up a macro or fuction,
> so that if you put:
>
> 1234mm will convert to cm and mm (123 cm 4 mm)
> 1234cm will convert it to M and cm (1 M 23.4 cm)
>
> you can play around with this.
>
> =IF(RIGHT(A1,2)="mm",QUOTIENT(LEFT(A1,LEN(A1)-2),10)&" cm
> "&MOD(LEFT(A1,LEN(A1)-2),10)&"
> mm",IF(RIGHT(A1,2)="cm",QUOTIENT(LEFT(A1,LEN(A1)-2),1000)&" M
> "&MOD(LEFT(A1,LEN(A1)-2),1000)/10&" cm"))
>
>
> --
> Bearacade
>
>
> ------------------------------------------------------------------------
> Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
> View this thread: http://www.excelforum.com/showthread...hreadid=559040
>
>

1234cm = 12m 34 cm NOT 1m 23.4 cm.

"Bearacade" wrote:

>
> I am not sure you are giving us the right example of what you want..
>
> 1234m does not equal to 1 M 23.4 CM
>
> nor does 1234cm = 123 cm 4 mm and vise versa..
>
> I did take what I think you meant and put this into a formula (it's not
> pretty, but it works) and you don't have to set up a macro or fuction,
> so that if you put:
>
> 1234mm will convert to cm and mm (123 cm 4 mm)
> 1234cm will convert it to M and cm (1 M 23.4 cm)
>
> you can play around with this.
>
> =IF(RIGHT(A1,2)="mm",QUOTIENT(LEFT(A1,LEN(A1)-2),10)&" cm
> "&MOD(LEFT(A1,LEN(A1)-2),10)&"
> mm",IF(RIGHT(A1,2)="cm",QUOTIENT(LEFT(A1,LEN(A1)-2),1000)&" M
> "&MOD(LEFT(A1,LEN(A1)-2),1000)/10&" cm"))
>
>
> --
> Bearacade
>
>
> ------------------------------------------------------------------------
> Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
> View this thread: http://www.excelforum.com/showthread...hreadid=559040
>
>

Thanks for helping us out..

Yeah that seems to work really well..

Much appreciated... !

"devo" <devo@i.dont.care> wrote in message
news:%23kCOLEToGHA.4728@TK2MSFTNGP05.phx.gbl...
> Hi
>
> i requre a little bit of guidance and help to right a simple function
>
> I have a value in milimeters which i want to pass to a function followed
> by a string or value to show that i want the result converting in to
>
> a) meters & cm - lets call it m
> b) cms & mm - lets call it cm
> c) mm - lets call it mm
>
> even though i passing the mm's to the function as a number i wish it to
> display it as a string
>
> thus 1234,m i wish to return as 1 m 23.4 cm
> or 1234,cm will return 123 cm 4 mms or 123.4 cms
> or 1234 mm will return 1234 mms
>
>
> I know this should be easy but i just cant seem how i should structure my
> login..
>
> was trying to use mod and fix .. i cant see why i can get my head round
> it.. as i done something similar for inches and passing the factor of the
> inches ... ie 1/8th 1/16th etc to it.. to display feet inches and
> denominator...
>
> please help..
>