Replacing Unwanted Text

[ocsicnar]

I've spent a good 1/2 day searching for the topic, but it either does not exist or I am not explaining it correctly. I hope someone can help.

I have a worksheet that tracks everyone's itinerary. If he/she is on "VC" (Vacation) or "SK" (Sick), the person is required to use specific codes (there are only 7 of them).

Can someone help me with a macro to find "unwanted" text and replace it with the word "OFFICE"? For example, someones itinerary says he'll be at Cincinnati on Monday and Tampa on Tuesday. I like to replace the city names with "OFFICE". Otherwise, the accepted codes remain intact.

So far, I only found macros with specific words to replace. But, tried to modify this one:

Sub ReplaceStrings()
Dim WantItems
Dim i As Long

WantItems = Array("VC", "JD", "CC", "FH", "BR", "HOLIDAY", "COMPANY HOLIDAY") '<-- extend as needed
Application.ScreenUpdating = False
​

With ActiveSheet.Range("F7:O32") 'Columns("A") <-- adjust to suit your column
​

For i = 0 To UBound(WantItems)
.Replace What:=IsNot WantItems(i), Replacement:="OFFICE", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next i​

End With​

Application.ScreenUpdating = True
End Sub​

 

[Scott Huish]

Sub test()
Dim c As Range
For Each c In Range("F7:O32")
Select Case UCase(c.Text)
    Case "VC", "JD", "CC", "FH", "BR", "HOLIDAY", "COMPANY HOLIDAY", ""
    Case Else
         c = "OFFICE"
End Select
Next
End Sub

 

[ocsicnar]

Wow! Awesome. I appreciate it.