Quote:
Originally Posted by Carchee
The conversions we use for pipe diameters are NPS and DN standards, specifically the numbers I had within the 'GetDiameter' sub. The following link will explain a bit further what I'm talking about if it interests you.
http://www.engineeringtoolbox.com/np...izes-d_45.html |
Some of those conversions for sizes less than 1in look decidedly dodgy to me. Equating 1/8in with 6mm whilst also equating 3/8 with 10mm defies logic (3*6mm = 18mm, not 10mm). And I've certainly bought 5/16in ID hose with both that and the metric equivalent (8mm) printed on it.
Try the following update. Note that I've added yet more conversions.
You'll need to tidy up more of your data, too. For example, you should remove any existing metric figures - you wouldn't want a double conversion for:
TRERICE AX9, 7 INCH (180mm)ADJUSTABLE ANGLE W/ THERMOWELL
In this case, I'd suggest deleting the (180mm). You might also want to add a 7in:180mm conversion to your diameters. The output would then become:
TRERICE AX9, 180 mm (7") ADJUSTABLE ANGLE W/ THERMOWELL
which is consistent with how the other conversions are done. Similarly, you'll need to clean up data like:
WIKA (TYPE PER INSTALLATION), LIQUID FILLED 2.5 INCH (63mm) DIA. 0-30 Hg/0-30 PSI RANGE
changing this to, say:
WIKA (TYPE PER INSTALLATION), LIQUID FILLED 2.5 INCH DIA. 0-30 Hg / 0-30 PSI RANGE
which would then be turned into:
WIKA (TYPE PER INSTALLATION), LIQUID FILLED 64 mm (2.5") DIA. 0-762mm (0-30") Hg 0-207 KPA (0-30 PSI) RANGE
Code:
Sub EQLConverter()
Dim lRow As Long, i As Long, j As Long, k As Long
Dim StrTmp As String, StrBit As String, StrConv As String, StrOut As String
With ActiveSheet.UsedRange
'Loop through each cell in column C
For i = 3 To .Cells.SpecialCells(xlCellTypeLastCell).Row
With .Cells(i, 3)
.Select
StrOut = ""
'Split the cell results by spaces for parsing
k = UBound(Split(.Text, " "))
'Process each 'word'
For j = 0 To k
StrTmp = Split(.Text, " ")(j)
If Len(StrTmp) > 0 Then
'See what the 'word' ends with
Select Case Right(StrTmp, 1)
'If it ends with ', try a ft:m conversion
Case "'"
StrConv = Replace(Split(StrTmp, "'")(0), "(", "")
If IsNumeric(StrConv) Then
StrOut = StrOut & " " & Format(StrConv * 0.3048, "0.0") & "m" & " (" & StrTmp & ")"
Else
StrOut = StrOut & " " & StrTmp
End If
'If it ends with ", try an in:mm conversion
Case """"
StrConv = Replace(Split(StrTmp, """")(0), "(", "")
StrConv = Evaluate(Replace(Replace(StrConv, "-", "+"), "'", "*12+0"))
If IsNumeric(StrConv) Then
StrOut = StrOut & " " & GetDiameter(CSng(StrConv)) & "mm" & " (" & StrTmp & ")"
Else
StrOut = StrOut & " " & StrTmp
End If
Case Else
Select Case Right(StrTmp, 2)
'If it ends with FT, try an ft:m conversion
Case "FT"
StrConv = Replace(Split(StrTmp, "FT")(0), "(", "")
If IsNumeric(StrConv) Then
StrOut = StrOut & " " & Format(StrConv * 0.3048, "0.0") & "m" & " (" & StrConv & "')"
Else
StrOut = StrOut & " " & StrTmp
End If
'If it ends with IN, try an in:mm conversion
Case "IN"
StrConv = Replace(Split(StrTmp, "IN")(0), "(", "")
StrConv = Evaluate(Replace(Replace(StrConv, "-", "+"), "'", "*12+0"))
If IsNumeric(StrConv) Then
StrOut = StrOut & " " & GetDiameter(CSng(StrConv)) & "mm" & " (" & StrConv & """)"
Else
StrOut = StrOut & " " & StrTmp
End If
Case Else
'If it's not ft/in and we haven't reached the end, look for other possibilities
If j < k Then
StrConv = Replace(Split(StrTmp, """")(0), "(", "")
If IsNumeric(StrConv) Then
'If the next 'word' is PSI, do a PSI:KPA conversion
If Left(Split(.Text, " ")(j + 1), 3) = "PSI" Then
StrOut = StrOut & " " & Round(StrConv * 6.894757, 0) & " KPA" & " (" & StrConv & " PSI)"
'If the next 'word' is IN/INCH, do an in:mm conversion
ElseIf Left(Split(.Text, " ")(j + 1), 2) = "IN" Then
StrConv = Evaluate(Replace(Replace(StrConv, "-", "+"), "'", "*12+0"))
StrOut = StrOut & " " & GetDiameter(CSng(StrConv)) & " mm" & " (" & StrConv & """)"
j = j + 1
'If the next 'word' is GPM, do a GPM:CMH conversion
ElseIf Left(Split(.Text, " ")(j + 1), 3) = "GPM" Then
StrOut = StrOut & " " & Format(StrConv * 3.785412, "0.0") & " L/Min" & " (" & StrConv & " GPM)"
j = j + 1
'If the next 'word' is GAL, do a Gal:Ltr conversion
ElseIf Left(Split(.Text, " ")(j + 1), 3) = "GAL" Then
Select Case StrConv * 3.785412
Case Is >= 100
StrOut = StrOut & " " & Format(StrConv * 0.3785412, "0") * 10 & " Ltr" & " (" & StrConv & " GAL)"
Case Is >= 10, Is < 100
StrOut = StrOut & " " & Format(StrConv * 3.785412, "0") & " Ltr" & " (" & StrConv & " GAL)"
Case Else
StrOut = StrOut & " " & Format(StrConv * 3.785412, "0.0") & " Ltr" & " (" & StrConv & " GAL)"
End Select
j = j + 1
'If the next 'word' is GPD, do a GPD:LPD conversion
ElseIf Left(Split(.Text, " ")(j + 1), 3) = "GPD" Then
Select Case StrConv * 3.785412
Case Is >= 100
StrOut = StrOut & " " & Format(StrConv * 0.3785412, "0") * 10 & " LPD" & " (" & StrConv & " GPD)"
Case Is >= 10, Is < 100
StrOut = StrOut & " " & Format(StrConv * 3.785412, "0") & " LPD" & " (" & StrConv & " GPD)"
Case Else
StrOut = StrOut & " " & Format(StrConv * 3.785412, "0.0") & " LPD" & " (" & StrConv & " GPD)"
End Select
j = j + 1
'If the next 'word' is G/HR, do a G/HR:L/HR conversion
ElseIf Left(Split(.Text, " ")(j + 1), 4) = "G/HR" Then
Select Case StrConv * 3.785412
Case Is >= 100
StrOut = StrOut & " " & Format(StrConv * 0.3785412, "0") * 10 & " LPH" & " (" & StrConv & " GPH)"
Case Is >= 10, Is < 100
StrOut = StrOut & " " & Format(StrConv * 3.785412, "0") & " LPH" & " (" & StrConv & " GPH)"
Case Else
StrOut = StrOut & " " & Format(StrConv * 3.785412, "0.0") & " LPH" & " (" & StrConv & " GPH)"
End Select
j = j + 1
'If the next 'word' is LB, do a LB:KG conversion
ElseIf Left(Split(.Text, " ")(j + 1), 2) = "LB" Then
Select Case StrConv / 2.20462
Case Is >= 100
StrOut = StrOut & " " & Format(StrConv / 2.20462, "0") * 10 & " KG" & " (" & StrConv & " LB)"
Case Is >= 10, Is < 100
StrOut = StrOut & " " & Format(StrConv / 2.20462, "0") & " KG" & " (" & StrConv & " LB)"
Case Else
StrOut = StrOut & " " & Format(StrConv / 2.20462, "0.0") & " KG" & " (" & StrConv & " LB)"
End Select
j = j + 1
'If the next 'word' is FT^2, do a FT^2:M^2 conversion
ElseIf Replace(Split(.Text, " ")(j + 1), ")", "") = "FT^2" Then
StrOut = StrOut & " " & Format(StrConv / 10.76391, "0.00") & " M^2" & " (" & StrConv & " FT^2)"
j = j + 1
'If the next two 'words' are SQ. FT., do a FT^2:M^2 conversion
ElseIf Left(Split(.Text, " ")(j + 1), 2) = "SQ" And Left(Split(.Text, " ")(j + 2), 2) = "FT" Then
StrOut = StrOut & " " & Format(StrConv / 10.76391, "0.00") & " M^2" & " (" & StrConv & " FT^2)"
j = j + 2
Else
StrOut = StrOut & " " & StrTmp
End If
' Test for a cell containing a numeric range
ElseIf InStr(StrConv, "-") > 0 Then
If IsNumeric(Split(StrConv, "-")(0)) And IsNumeric(Split(StrConv, "-")(1)) Then
'If the next 'word' is PSI, do a PSI:KPA conversion
If Left(Split(.Text, " ")(j + 1), 3) = "PSI" Then
StrOut = StrOut & " " & Round(Split(StrConv, "-")(0) * 6.894757, 0) & "-" & Round(Split(StrConv, "-")(1) * 6.894757) & " KPA" & " (" & StrConv & " PSI)"
j = j + 1
End If
'If the next 'word' begins with IN or Hg, do an in:mm conversion
If Left(Split(.Text, " ")(j + 1), 2) = "Hg" Then
StrOut = StrOut & " " & Round(Split(StrConv, "-")(0) * 25.4, 0) & "-" & Round(Split(StrConv, "-")(1) * 25.4, 0) & "mm" & " (" & StrConv & """) Hg"
j = j + 2
End If
If Left(Split(.Text, " ")(j + 1), 2) = "IN" Then
StrOut = StrOut & " " & Round(Split(StrConv, "-")(0) * 25.4, 0) & "-" & Round(Split(StrConv, "-")(1) * 25.4, 0) & "mm" & " (" & StrConv & """)"
j = j + 2
End If
End If
Else
StrOut = StrOut & " " & StrTmp
End If
Else
StrOut = StrOut & " " & StrTmp
End If
End Select
End Select
Else
StrOut = StrOut & " "
End If
Next
StrOut = Trim(StrOut)
If InStr(StrOut, " X ") > 0 Then
StrConv = ""
For j = 0 To UBound(Split(StrOut, " X ")) Step 2
StrTmp = Split(StrOut, " X ")(j)
For k = 0 To UBound(Split(StrTmp, " ")) - 1
StrConv = StrConv & " " & Split(StrTmp, " ")(k)
Next
StrConv = StrConv & " * " & Split(Split(StrOut, " X ")(j + 1), " ")(0)
StrConv = StrConv & " " & Replace(Split(StrTmp, " ")(k), ")", " x ")
StrConv = StrConv & Replace(Split(Split(StrOut, " X ")(j + 1), " ")(1), "(", "")
Next
For j = 1 To UBound(Split(StrOut, " X ")) Step 2
StrTmp = Split(StrOut, " X ")(j)
For k = 2 To UBound(Split(StrTmp, " "))
StrConv = StrConv & " " & Split(StrTmp, " ")(k)
Next
Next
StrOut = StrConv
End If
.Value = Trim(StrOut)
End With
Next
End With
End Sub
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
|