Paul,
I figured I would need to use some sort of look up table for the in:mm conversion, I just didn't know how to implement it.
Of course I forgot one more piece of equipment, a basket strainer, you can see it added to the list in the attachment. I double checked all my equipment and made sure we didn't have any more. I tried implementing it into the code and couldn't get it to work. What I have shown I know is wrong and doesn't even make sense to me, I was just trying everything. I really am trying to understand this stuff, I think my problem is that I only understand parts of the code and not all of it. So it's hard for me to add new code beyond my knowledge, which is very limited.
I get 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. I tried playing with it and couldn't get it to work. All the code works perfectly, converts and does exactly what I want it to with the exception of that one error.
Also, you mentioned the compressor and its units. Although you are correct a compressor would have a volume unit the actual unit given is a filter surface area. The note states that a compressor is included. The note is a little misleading if you aren't used to working with it. I changed the code to account for the area. Good eye!
My current code:
Code:
Sub EQLConverter()
Dim lRow As Long, i As Long, j As Long, k As Long, StrTmp 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"
StrConv = Replace(Split(StrTmp, "IN")(0), "(", "")
StrConv = Evaluate(Replace(Replace(StrConv, "-", "+"), "'", "*12+0"))
If IsNumeric(StrConv) Then
StrOut = StrOut & " " & GetDiameter(CSng(StrConv)) & "mm" & " (" & StrTmp & " X " & StrTmp & ")"
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 / 1000 * 3.78544 * 60, "0.0") & " CMH" & " (" & StrConv & " GPM)"
j = j + 1
ElseIf Left(Split(.Text, " ")(j + 1), 3) = "GAL" Then
StrOut = StrOut & " " & Format(StrConv / 1000 * 3.78544, "0.0") & " M^3" & " (" & StrConv & " GAL)"
j = j + 1
ElseIf Replace(Split(.Text, " ")(j + 1), ")", "") = "FT^2" Then
StrOut = StrOut & " " & Format(StrConv / 1000 * 9.290304, "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 / 1000 * 9.290304, "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
Else
StrOut = StrOut & " "
End If
Next
.Value = 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.0")
End Select
End Function
Thank you Paul.
09-18-2014, 09:02 AM
Similar Threads
Convert numbers to a specific text string
Windows 7 64bit 
Office 2007
Hybrid Mode