Ignoring a Special Character-CodePudding

[![enter image description here][1]][1]I'll try to make this short and sweet. I'm making an inspection sheet and I'm running into an issue with angular dimensions. My evaluate macro won't work on these and I'm sure it's because of the "°" character. When it comes across a cell with that character present, is there a way to truncate or ignore the "°" so the rest of the loop can be run as intended?

I was hoping to avoid this because it feels like showing my finger paintings to a bunch of Rembrandts, but here's a chunk of the evaluation code. This covers non-numeric entries in the "Shop Print Max" column (max and min columns are generally merged into one cell in these cases) and numeric entries in just the "Shop Print Max" column. Please be gentle. I didn't even know how to access the editor until a little more than a month ago.

Sub Evaluate_Pre_Forge()
Dim R As Integer
Dim R2 As Integer
Dim Rng As Range
R = 12
R2 = 13
Do While (R < 70)

'(Text Entries in Column "N")
If (IsNumeric(Cells(R, 14))) = False Then
  For Each Rng In Range(("T" & R), ("W" & R2))
    If (Cells(R, 8) = "Y") = True Or (Cells(R, 8) = "y") = True Then
      If Not IsEmpty(Rng) Then
        If (IsNumeric(Rng)) = False And (Rng <> "Conforms") = True Then
          Rng.Interior.Color = 16777215
        ElseIf (IsNumeric(Rng)) = False And (Rng = "Conforms") = True Then
          Rng.Interior.Color = 7862528
        End If
      End If
    End If
  Next Rng
ElseIf (IsNumeric(Cells(R, 14))) = True Then
'(Numeric Value in Column "N" Only)
  If (Cells(R, 14).Value > 0) = True And (Cells(R, 17).Value <= 0) = True Then
    For Each Rng In Range(("T" & R), ("W" & R2))
      If (Cells(R, 8) = "Y") = True Or (Cells(R, 8) = "y") = True Then
        If Not IsEmpty(Rng) Then
          If (IsNumeric(Rng)) = True Then
            '(Max Value Greater Than 100)
            If Cells(R, 14).Value >= 100 Then
              If (Rng.Value >= 100) Then
                If Cells(R, 14).Value >= Rng.Value Then
                  Rng.Interior.Color = 7862528
                End If
              End If
              If (Rng.Value < 100) And (Rng.Value >= 10) Then
                Rng.Interior.Color = 7862528
              End If
              If (Rng.Value < 10) And (Rng.Value >= 0) Then
                Rng.Interior.Color = 7862528
              End If
            End If
            '(Max Value Between 10 and 100)
            If Cells(R, 14).Value < 100 And Cells(R, 14).Value >= 10 Then
              If (Rng.Value < 100) And (Rng.Value >= 10) Then
                If Cells(R, 14).Value >= Rng.Value Then
                  Rng.Interior.Color = 7862528
                End If
              End If
              If (Rng.Value < 10) And (Rng.Value >= 0) Then
                Rng.Interior.Color = 7862528
              End If
            End If
            '(Max Value Between 0 and 10)
            If Cells(R, 14).Value < 10 Then
              If (Rng.Value < 10) And (Rng.Value >= 0) Then
                If Cells(R, 14).Value >= Rng.Value Then
                  Rng.Interior.Color = 7862528
                End If
              End If
            End If
          ElseIf (IsNumeric(Rng)) = False And Rng = "Conforms" Then
            Rng.Interior.Color = 7862528
          End If
        End If
      End If
    Next Rng
  End If

End If
R = R   2
R2 = R2   2
Loop

End Sub




  [1]: https://i.stack.imgur.com/4rQaO.png

CodePudding user response：

What about Replace([YourReferenceHere], "°", "")? This will replace the special character with an empty string.

CodePudding user response：

You can easily check with an if statement. See below for example:

cv = Sheet1.Cells(1, 1)

If Right(Trim(cv), 1) = "°" Then
   cv = Left(Trim(cv), Len(cv) - 1)
End If

CodePudding user response：

You can simply use val function. That returns the numbers contained in a string as a numeric value of appropriate type.

About val function

Sample vba:

Dim MyValue
MyValue = Val("100.5°")    ' Returns 100.5