Try the following update. Incorporating strainer-type dimensions required a fair bit more code.
I don't understand your reference to;
Quote:
|
a "Run-Time error '9': Subscript out of range", it only pops up when the filter type with SQ. FT. units aren't in the list
|
as there's no list for these anyway and I'm not getting an error with the code I posted.
Your 'IN' conversion code wouldn't have worked because it was embedded inside the part of the Select Case statement that only looked at the right-most character, whereas you need to test the two right-most characters.
Some of your conversion factor changes were wrong - I've corrected those too.
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 a 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
'If it ends with IN, try a in:mm conversion
Case "IN"
Case Else
Select Case Right(StrTmp, 2)
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
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 GPM, do a GPM:CMH conversion
If Left(Split(.Text, " ")(j + 1), 3) = "GPM" Then
StrOut = StrOut & " " & Format(StrConv * 0.003785412 * 60, "0.0") & " CMH" & " (" & StrConv & " GPM)"
j = j + 1
ElseIf Left(Split(.Text, " ")(j + 1), 3) = "GAL" Then
StrOut = StrOut & " " & Format(StrConv * 0.003785412, "0.00") & " M^3" & " (" & StrConv & " GAL)"
j = j + 1
ElseIf Replace(Split(.Text, " ")(j + 1), ")", "") = "FT^2" Then
StrOut = StrOut & " " & Format(StrConv * 0.09290304, "0.00") & " M^2" & " (" & StrConv & " FT^2)"
j = j + 1
ElseIf Left(Split(.Text, " ")(j + 1), 2) = "SQ" And Left(Split(.Text, " ")(j + 2), 2) = "FT" Then
StrOut = StrOut & " " & Format(StrConv * 0.09290304, "0.00") & " M^2" & " (" & StrConv & " FT^2)"
j = j + 2
Else
StrOut = StrOut & " " & StrTmp
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
Function GetDiameter(sngDia As Single)
Select Case sngDia
Case Is = 0.125: GetDiameter = 6
Case Is = 0.1875: GetDiameter = 7
Case Is = 0.25: GetDiameter = 8
Case Is = 0.375: GetDiameter = 10
Case Is = 0.5: GetDiameter = 15
Case Is = 0.625: GetDiameter = 18
Case Is = 0.75: GetDiameter = 20
Case Is = 1: GetDiameter = 25
Case Is = 1.25: GetDiameter = 32
Case Is = 1.5: GetDiameter = 40
Case Is = 2: GetDiameter = 50
Case Is = 2.5: GetDiameter = 65
Case Is = 3: GetDiameter = 80
Case Is = 4: GetDiameter = 100
Case Is = 5: GetDiameter = 125
Case Is = 6: GetDiameter = 150
Case Is = 8: GetDiameter = 200
Case Is = 10: GetDiameter = 250
Case Is = 12: GetDiameter = 300
Case Is = 14: GetDiameter = 350
Case Is = 16: GetDiameter = 400
Case Is = 18: GetDiameter = 450
Case Is = 20: GetDiameter = 500
Case Is = 24: GetDiameter = 600
Case Is = 30: GetDiameter = 750
Case Else: GetDiameter = Format(sngDia * 25.4, "0")
End Select
End Function
FWIW, You can download a free unit converter (Convert) from:
http://joshmadison.com/convert-for-windows/