Complex Find and Convert

[Drumin_Phreak]

Hello,

I am currently trying to devise a way (using excel and vba), that whenever a number is entered followed by a string of text typically a imperial measurement abbreviation ("in", "ft", "Yrd"), The number is converted to a metric equivalent and the abbreviation is deleted. Here is a sample of the data I am working with:

Any kind of help would be appreciated!

 

[Rick Rothstein]

The number is converted to a metric equivalent

What exactly do you mean by "metric equivalent"... won't all of those abbreviations ("in", "ft", "Yrd") convert to the same metric dimension? If so, which metric equivalent is it (meters)? If not, then I think we need a little more information about what you are looking for.

 

[Drumin_Phreak]

What exactly do you mean by "metric equivalent"... won't all of those abbreviations ("in", "ft", "Yrd") convert to the same metric dimension? If so, which metric equivalent is it (meters)? If not, then I think we need a little more information about what you are looking for.

Rick,

I am sorry if I did not explain myself. In the company I work for we do everything in metric. However, sometimes orders come in in imperial. When they do, it is usually denoted that it is imperial, by the text preceding the numeric value. Inches will covert to MM Yds convert to Meters. You will have to excuse me I am not real sure why I put feet in there. We only deal with Meters (yards) and Millimeters (Inches).

I hope this has clarified it a bit better for you and i appreciate your willingness to help me with this problem.

 

[Rick Rothstein]

Give this event code a try (note that you didn't say, so I assumed those values are in Columns B and C, hence the If test for the column being 2 or 3)...

[table="width: 500"]
[tr]
	[td]Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column = 2 Or Target.Column = 3 Then
    If InStr(1, Target.Value, "yrd", vbTextCompare) Or InStr(1, Target.Value, "yd", vbTextCompare) Then
      Target.Value = Val(Target.Value) * 0.9144
    ElseIf InStr(1, Target.Value, "in", vbTextCompare) Then
      Target.Value = Val(Target.Value) * 25.4
    End If
  End If
End Sub[/td]
[/tr]
[/table]

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Drumin_Phreak]

Give this event code a try (note that you didn't say, so I assumed those values are in Columns B and C, hence the If test for the column being 2 or 3)...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column = 2 Or Target.Column = 3 Then
    If InStr(1, Target.Value, "yrd", vbTextCompare) Or InStr(1, Target.Value, "yd", vbTextCompare) Then
      Target.Value = Val(Target.Value) * 0.9144
    ElseIf InStr(1, Target.Value, "in", vbTextCompare) Then
      Target.Value = Val(Target.Value) * 25.4
    End If
  End If
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Rick,


This worked like a champ! I cannot thank you enough!