Find Replace VBA

[doug firr]

I have a column called "specialty" with about 2,000 rows. Each row can be one of about 20 specialties (the specialties are professions, such as analyst, chef etc etc).

Each of these specialties must be re-grouped further into 5 upper level headings (e.g. office, services, trade etc)

What I have to do each reporting period is do find/replace in excel for each of the 20 categories. e.g. find analyst and replace with office.

I could do a vlookup but I want to start familiarizing myself with VBA.

Is there a piece of code some generous mr excel boards user could provide to save me from doing this each time?

 

[jeffreybrown]

Hi Paul in Philly,

How about this as a starter...

Sub UpdateTitles()
    Dim RepWhat As String
    Dim RepWith As String
    
    Application.ScreenUpdating = False
    
    RepWhat = InputBox("Find what?")
    RepWith = InputBox("Replace with what")

    With Sheets("Sheet1")
        .Cells.Replace What:=RepWhat, Replacement:=RepWith, LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
    End With
End Sub

 

[doug firr]

Hi Jeff thanks for your response. I'm new to VBA (understatement) so please be patient with me. May I repeat the rows "repwhat +... and repwith =..." numerous times? I have about 20 find replaces to do - is it correct to just repeat these in the same format?
thanks again
Paul

 

[jeffreybrown]

Hi Paul,

Yes you can repeat the code as many times as necessary, basically one change at a time.

My first thought was some type of array to list your 20 titles, but not sure how you would tie one title to the specific replacement of another title.

I keep thinking about it or maybe somebody more advanced will step in for assistance, but in the end, a one-for-one swap may be the only way.

http://www.rondebruin.nl/code.htm

 

[doug firr]

Thanks Jeff, will give it a try in the afternoon and will let you know how I get on. Cheers!

 

[jeffreybrown]

Hi Paul,

Here is an update. Just fill in the array's with what you have and what you want and then run.

Sub ChangeTitles()
    Dim Rng As Range, RepWhat(), RepWith(), i As Integer

    Set Rng = Sheets("Sheet1").UsedRange

    RepWhat = Array("nurse", "cop", "chef") 'Change titles here
    RepWith = Array("doctor", "lawyer", "baker") 'Change titles here
    
    Application.ScreenUpdating = False

    For i = LBound(RepWhat) To UBound(RepWhat)
        Rng.Replace What:=RepWhat(i), Replacement:=RepWith(i), _
            LookAt:=xlPart, SearchOrder:=xlByRows
    Next i

    Application.ScreenUpdating = True
    
End Sub

 

[Dartagnan]

Hello just wanted to give you another way to go. A little more leg work up front, but once the find and replace are entered you would only to select the column and the macro will do the find/replace.

Sub Update()

Dim cll As Range
Set cll = Application.InputBox(Prompt:="SELECT THE COLUMNS TO USE", Type:=8)
cll.Select
If cll Is Nothing Then Exit Sub

For Each cll In Selection
If IsEmpty(cll.Value) Then GoTo Line99
If cll.Value = "Analyst" Then
Selection.Replace What:="Analyst", Replacement:="Office", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ElseIf cll.Value = "Chef" Then
Selection.Replace What:="Chef", Replacement:="Kitchen", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End If
Line99:
Next cll
End Sub

 

[Shawnyv]

Perhaps I'm misunderstanding the question, but these solutions seem like they require a lot of hard-coding that could better be taken care of in a simple loop.

IE: If the categories are the same on a month-to-month basis, then you can create a second worksheet that has the original names in column A (no repeats), and the names they have to be replaced with.

Then just create a while loop that runs until there are no new values in the original names column in the new worksheet. Run through second sheet and assign the old name to value "a", the new name to value "b", then go back to sheet 1 and do a search & replace utilizing values "a" and "b". Rinse and repeat until done.

A tiny bit of code, and easily changeable as new values are added/changed/etc

 

[jeffreybrown]

Then please feel free to provide that tiny bit of code. Remember, not everybody may have the same knowledge you posses.

 

[Shawnyv]

Sorry - I always feel like I'm the least knowledgable guy on the board.

So here's the prep for this - tab named "Sheet1" has the values to be replaced

Sheet named "Sheet2" has the replacement "code" (I'm assuming that these don't change radically month to month). The "code" is simple - column A has the category to be changed, column B has the replacement value.

    Sheets("Sheet2").Select
    Range("A1").Select
    Do While ActiveCell.Value <> ""
         a = ActiveCell.Value
         b = ActiveCell.Offset(0, 1).Value
         ActiveCell.Offset(1, 0).Select
         Sheets("Sheet1").Select
         Cells.Replace What:=a, Replacement:=b, LookAt:=xlPart, _
             SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
             ReplaceFormat:=False
         Sheets("Sheet2").Select
    Loop
    sheets("Sheet1").select

The benefits of this are that you can dynamically change the values, add values, remove values, etc as time goes on without having to touch the VBA.