Ignore special character in string in VBA

hajiali

Active Member
Joined
Sep 8, 2018
Messages
416
Office Version
  1. 2016
Platform
  1. Windows
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.

VBA Code:
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
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,928
Office Version
  1. 2007
Platform
  1. Windows
Try this:

VBA Code:
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
 
Solution

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,928
Office Version
  1. 2007
Platform
  1. Windows
I'm glad to help you. Thanks for the feedback.
 

hajiali

Active Member
Joined
Sep 8, 2018
Messages
416
Office Version
  1. 2016
Platform
  1. Windows
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.

VBA Code:
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
 

Forum statistics

Threads
1,147,962
Messages
5,744,059
Members
423,843
Latest member
alex2022

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top