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.
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