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?

 

[Dartagnan]

Reading Shawnyv posts I started thinking how this could be done with a vlookup, but I know you want to replace the values. So I came up with a combination (see below) of both.

FYI. In cells A1 to A3, of sheet 1, I have Analyst, Chef and Nurse
In Sheet 2, I have Analyst, Chef, and Nurse in cells A1 to A3 and Office, Kitchen, and Hospital in cells B1 to B3.

Here is the code which will do a vlookup using array in sheet 2 and perform a Find/Replace on the same column you highlighted to do the lookup.

Sub Update()

Dim rng As Range
Dim look As Range
Dim col As Integer
Dim found As Variant

Set look = Application.InputBox(Prompt:="SELECT COLUMN TO REPLACE", Type:=8)
look.Select
Set rng = Sheets("Sheet2").Columns("A:B") 'basically a lookup table
col = 2
If look Is Nothing Then Exit Sub
For Each look In Selection
If IsEmpty(look.Value) Then GoTo Line99
found = Application.WorksheetFunction.VLookup(look, rng, col, o)
Cells.Replace What:=look, Replacement:=found, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Line99:
Next look

End Sub

 

[Rick Rothstein]

@Shawnyv,

If I understand your code (and the setup it applies to) correctly, I believe this shorter piece of code will do the same thing...

Dim C As Range
For Each C In Sheets("Sheet2").Columns("A").SpecialCells(xlCellTypeConstants)
  Sheets("Sheet1").Cells.Replace C.Value, C.Offset(0, 1).Value
Next

The important thing to take away from my code sample is that it is almost never necessary to select sheets and cells on those sheets in order to manipulate the cells themselves in some way... you normally can refer to the sheet and/or cell object properties and methods directly rather than selecting sheets and cells only to then refer to the properties and methods of the ActiveCell instead.

 

[Shawnyv]

Thx for that - much more elegant & useful.

See, that's why I always feel like the dumbest guy on the board.