Search a cell that contains words and numbers and convert the numbers to metric

Carchee · #8 09-18-2014, 09:02 AM

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.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Words in my document were converted to numbers????	MikeD23	Word	1	08-26-2012 11:09 AM
can word convert numbers?	koolfire	Word	2	01-07-2012 02:12 PM
Convert numbers to a specific text string	francis	Excel	1	10-06-2011 01:43 PM
Numbers in Words	janak	Excel	2	12-19-2010 08:53 PM
Numbers Convert in word in MS Access	towhid	Office	0	08-19-2010 01:17 AM