Metric conversion formula / function

[Paula ;)]

Hi all,

I'm modifying a worksheet for our Quality Control dept. where I have
to take metric dimensions (millimeters) off a print and convert them
into English / Imperial (inches) for the guys to check parts from.


Anybody know if there is a way to enter a metric dimension and have
it
automatically convert to English / Imperial rather than doing the
conversion on a calculator and entering the converted number in
manually?


TIA,
Paula

 

[JE McGimpsey]

One way:

=CONVERT(A1,"mm","in")

which requires the Analysis Toolpak Add-in to be loaded
(Tools/Addins...) for versions prior to XL07.

Of course, you could also use

=A1/25.4

 

[Gord Dibben]

Automatic would imply VBA code.

This event code will convert mm to inches as you enter the mm in any cell in the
range A1:A20

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const the_range As String = "A1:A20"
If Not IsNumeric(Target.Value) Then Exit Sub
If Not Intersect(Target, Me.Range(the_range)) Is Nothing Then
Application.EnableEvents = False
With Target
.Value = .Value / 25.4
Application.EnableEvents = True
End With
End If

End Sub


Gord Dibben MS Excel MVP

 

[JE McGimpsey]

Automatic would imply VBA code.

Very true, and most probably what the OP wants. One downside of that is
that the original data is not retained. In most cases I'd recommend
using the conversion in follow-on calculations...

 

[Paula ;)]

Thanks for the response.

So would I just place this code anywhere in the worksheet out of the
print range?

TIA,
Paula

 

[Paula ;)]

Thanks for the response.

Yea, I saw that somewhere. I was looking for something a little more
"automatic" than that. Possibly the response that's shown below
yours. Just have to figure out how to use it properly.

Thanks again,
Paula

 

[Gord Dibben]

This is sheet event code.

Right-click on the sheet tab and "View Code". Copy/paste the code into that
sheet module.

You can change CONST the_range as String to a larger or different range of
cells.

John's caveat about losing the original data is a valid point and should be
considered if you need a paper trail for error checking. Once you have entered
a number in mm and it gets converted to inches, you cannot undo if you have made
a mistake in entry.


Gord

 

[Gord Dibben]

Good point John.

No paper trail can lead to errors.


Gord

 

[Gord Dibben]

Here is a revised code to give you a paper trail.

You will need an empty cell right of the target cell for the converted number to
show up, leaving the original mm in the cell in which you entered it.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const the_range As String = "A1:A20"
If Not IsNumeric(Target.Value) Then Exit Sub
If Not Intersect(Target, Me.Range(the_range)) Is Nothing Then
On Error GoTo endit
Application.EnableEvents = False
With Target.Offset(0, 1)
.Value = Target.Value / 25.4
End With
End If
endit:
Application.EnableEvents = True
End Sub


Gord