Ignore special character in string in VBA

[hajiali]

In the code below I want to add functional to ignore "(T)" or "(D)" or "(S)" or "*" or in the string if it exists.

Ex:

if a name in Column C is JOHN S. and the name in column V name appears JOHN S.*
the name JOHN S. should be removed per code.

or

Column I has PETER (T)(D)(S) and the name in column V name appears PETER (T)
the name PETER should be removed per code.

if an "*" ,"(T)" , "(D)" , "(S)" appears it will always appear at the end of all names. Names can have any variation of these specials characters.

ex. there can be
John S. (T)(D)(S)
John S. (T)(D)
John S. (T)
John S. (D)(S)
John S. (D)
John S. (S)
John S. (T)(D)(S)*
John S. (T)(D)*
John S. (T)*
John S. (D)(S)*
John S. (D)*
John S. (S)*

In other words apply code to any names ignoring "(T)" or "(D)" or "(S)" or "*"

Any help is greatly appreciated. let me know if this makes since.

Sub UpdateBlackBook()
    Dim arr
    Dim rngI As Range, rngC As Range, Rng As Range
    Dim r As Long, c As Long, i As Long, x As Long
    'REMOVE LEAVE LIST
    arr = Range("V4:V75")
    Set rngC = Range("C4:C" & Cells(Rows.Count, "C").End(xlUp).Row)
    Set rngI = Range("I4:I" & Cells(Rows.Count, "I").End(xlUp).Row)
    For r = 1 To 72
        For c = 1 To 1
            If Not arr(r, c) = "" Then
                For i = 1 To rngC.Cells.Count
                    If rngC.Cells(i) = arr(r, c) Then
                        rngC.Cells(i) = ""
                        rngC.Cells(i).Interior.ColorIndex = 6
                    End If
                Next
                For x = 1 To rngI.Cells.Count
                    If rngI.Cells(x) = arr(r, c) Then
                        rngI.Cells(x) = ""
                        rngI.Cells(x).Interior.ColorIndex = 6
                    End If
                Next
            End If
        Next
    Next

 

[DanteAmor]

Try this:

Sub UpdateBlackBook()
  Dim arr As Variant
  Dim rng As Range, c As Range
  Dim i As Long
  'REMOVE LEAVE LIST
  arr = Range("V4:V75").Value
  For i = 1 To UBound(arr)
    arr(i, 1) = Trim(Replace(Replace(Replace(Replace(arr(i, 1), "(T)", ""), "(D)", ""), "(S)", ""), "*", ""))
  Next
  Set rng = Range("C4:C" & Cells(Rows.Count, "C").End(3).Row & ",I4:I" & Cells(Rows.Count, "I").End(3).Row)
  For i = 1 To UBound(arr)
    If arr(i, 1) <> "" Then
      For Each c In rng
        If Trim(Replace(Replace(Replace(Replace(c.Value, "(T)", ""), "(D)", ""), "(S)", ""), "*", "")) = arr(i, 1) Then
          c.Value = ""
          c.Interior.ColorIndex = 6
        End If
      Next
    End If
  Next
End Sub

 

[hajiali]

Much Appreciate it DanteAmor. Works great

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[hajiali]

Hello DanteAmor I have similar issues with the following code. the columns that I need to Ignore "(T)" or "(D)" or "(S)" or "*" are columns C, Column I and Column R any suggestion is Greatly Appreciated.

Sub ShiftTrades()
Dim i, j, lrn, lrb As Long

lrn = Cells(Rows.Count, "C").End(xlUp).Row

For i = 1 To lrn
    For j = 4 To 135
    If Cells(i, "C").Value = Cells(j, "R").Value And Left(Cells(j, "Q").Value, 1) = "S" And Cells(j, "N").Value = Cells(i, "D").Value Then
        Cells(i, "C").Value = Cells(j, "P").Value
        Cells(i, "C").Value = Trim(Cells(i, "C"))
    End If
     If Cells(i, "I").Value = Cells(j, "R").Value And Left(Cells(j, "Q").Value, 1) = "S" And Cells(j, "N").Value = Cells(i, "H").Value Then
        Cells(i, "I").Value = Cells(j, "P").Value
        Cells(i, "I").Value = Trim(Cells(i, "I"))
    End If
    If Cells(i, "C").Value = Cells(j, "R").Value And Left(Cells(j, "Q").Value, 1) = "T" And Cells(j, "N").Value = Cells(i, "D").Value Then
        Cells(i, "C").Value = Cells(j, "P").Value
        Cells(i, "C").Value = Trim(Cells(i, "C"))
    End If
    If Cells(i, "I").Value = Cells(j, "R").Value And Left(Cells(j, "Q").Value, 1) = "T" And Cells(j, "N").Value = Cells(i, "H").Value Then
        Cells(i, "I").Value = Cells(j, "P").Value
        Cells(i, "I").Value = Trim(Cells(i, "I"))
    End If
    If Cells(i, "C").Value = Cells(j, "R").Value And Left(Cells(j, "Q").Value, 1) = "P" Then
        Cells(i, "C").Interior.Color = RGB(112, 173, 71)
        Cells(i, "C").Font.Color = RGB(255, 255, 255)
        Cells(i, "C").Font.Bold = True
    End If
    If Cells(i, "I").Value = Cells(j, "R").Value And Left(Cells(j, "Q").Value, 1) = "P" Then
        Cells(i, "I").Interior.Color = RGB(112, 173, 71)
        Cells(i, "I").Font.Color = RGB(255, 255, 255)
        Cells(i, "I").Font.Bold = True
    End If
    If Cells(i, "C").Value = Cells(j, "R").Value And Left(Cells(j, "Q").Value, 1) = "P" Then
        Cells(i, "C").Interior.Color = RGB(112, 173, 71)
        Cells(i, "C").Font.Color = RGB(255, 255, 255)
        Cells(i, "C").Font.Bold = True
    End If
    If Cells(i, "I").Value = Cells(j, "R").Value And Left(Cells(j, "Q").Value, 1) = "P" Then
        Cells(i, "I").Interior.Color = RGB(112, 173, 71)
        Cells(i, "I").Font.Color = RGB(255, 255, 255)
        Cells(i, "I").Font.Bold = True
    End If
    Next j
Next i
End Sub