Convert Feet to Milimeters (Ft to MM) and also with operators

[MSRoach]

I have written the following routine that will convert the range that I've selected from Feet to MM. The problem is that I've found that there are a lot of ranges that have operators such as: <30 or 10 - 20 or >60. I need to parse this text so that the result is a string that has the same operators (<,>,-), but the numerical values are converted.

Examples:

"<30" returns "<9144"
"10 - 20" returns "3048 - 6096"

The numerical conversion:

Public Sub Ft_to_mm()
    
    Dim SelRange As Range
    Dim C As Range
    
    Set SelRange = Selection
    
    For Each C In SelRange
        If IsNumeric(C) And C <> "" Then    'Range contains a number to convert
                C.Value = C.Value * 304.8
                C.NumberFormat = "0"
        Else                                'Range contains <, >, -...
                                            'break up the string
                                            
              '  "CODE HERE ?"
                                            
        End If
      Next
End Sub

 

[Rick Rothstein]

I have written the following routine that will convert the range that I've selected from Feet to MM. The problem is that I've found that there are a lot of ranges that have operators such as: <30 or 10 - 20 or >60. I need to parse this text so that the result is a string that has the same operators (<,>,-), but the numerical values are converted.

Are you numbers always composed of digits only? Or could have comma delimited values or numbers in powers of ten (scientific notation)?

 

[MSRoach]

Are you numbers always composed of digits only? Or could have comma delimited values or numbers in powers of ten (scientific notation)?

The number portion of the string is only numbers. <12,340 does not exist. It is always in the form N, *N, N*, N*N where N is a number without any notation such as commas or exponential information. * is a string that should be repeated back after the conversion. So the answer is in the form CN, *CN, CN*, CN*CN where CN is a converted number.

 

[Rick Rothstein]

The number portion of the string is only numbers. <12,340 does not exist. It is always in the form N, *N, N*, N*N where N is a number without any notation such as commas or exponential information. * is a string that should be repeated back after the conversion. So the answer is in the form CN, *CN, CN*, CN*CN where CN is a converted number.

Okay, see if this macro does what you want...

Public Sub Ft_to_mm()
  Dim C As Range, X As Long, Original As String, Temp As String, FT() As String, MM() As String
  For Each C In Selection
    Original = C.Value
    Temp = Original
    For X = 1 To Len(Temp)
      If Mid(Temp, X, 1) Like "[!0-9]" Then
        Mid(Temp, X) = " "
      End If
    Next
    FT = Split(WorksheetFunction.Trim(Temp))
    ReDim MM(0 To UBound(FT))
    If UBound(FT) > -1 Then
      For X = 0 To UBound(FT)
        MM(X) = CStr(FT(X) * 304.8)
      Next
    End If
    For X = 0 To UBound(FT)
      Original = WorksheetFunction.Replace(Original, InStr(Original, FT(X)), Len(FT(X)), MM(X))
    Next
    C.Value = Original
  Next
End Sub

 

[MSRoach]

Thank you -

This works unless there is a merged cell. In that case it's actually recycling the result from the first run.

Also, can you set the format of the number to "0". It is out putting "0.0"

Thanks again