 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / September 2007Metric conversion formula / function
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 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 > Hi all, > [quoted text clipped - 10 lines] > TIA, > 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 > One way: > [quoted text clipped - 25 lines] > > - Show quoted text - 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 >Hi all, > [quoted text clipped - 10 lines] >TIA, >Paula > 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... Good point John. No paper trail can lead to errors. Gord >> 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... Thanks for the response. So would I just place this code anywhere in the worksheet out of the print range? TIA, Paula > Automatic would imply VBA code. > [quoted text clipped - 32 lines] > > - Show quoted text - 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 >Thanks for the response. > [quoted text clipped - 40 lines] >> >> - Show quoted text - 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 >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.
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.