Find and Replace/Look up

[Mr_Philip]

Hi!

Ok, so I have a list of cost centres e.g 2000,2411,2582
these have been replaced with new cost centre numbers e.g.
8500,8548,8449

so that now:

2000 = 8500
2411 = 8548
2582 = 8449.

I will have spreadsheets submitted where the old cost centres will be
referenced.

What I want to do is; search the spreadsheet for the old cost centres,
and replace the value with the corresponding new cost centres.
something faster than a simple find and replace because there are
hundreds of cost centres.

 

[Portuga]

The simple find and replace option also gives you the option to "replac
all"

go to "Edit" select "Replace" select the values you want and then clic
"Replace all".

fas

 

[Dave Peterson]

There are no new numbers that matched any existing numbers--if there are, don't
use this!

Create a new workbook with a single worksheet with the old values in column A
and the new values in column B.

Option Explicit
Sub testme()

Dim wkbk As Workbook
Dim myRng As Range
Dim myCell As Range
Dim wks As Worksheet

With ThisWorkbook.Worksheets("Sheet1")
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

Set wkbk = ActiveWorkbook
If wkbk.FullName = ThisWorkbook.FullName Then
MsgBox "Please activate the workbook to be fixed!"
Exit Sub
End If

For Each wks In wkbk.Worksheets
For Each myCell In myRng.Cells
With wks.UsedRange
.Replace what:=myCell.Value, _
replacement:=myCell.Offset(0, 1).Value, _
lookat:=xlWhole, _
searchorder:=xlByRows, _
MatchCase:=False
End With
Next myCell
Next wks
End Sub

And I assumed that the cost centers were in cells by themselves (xlwhole--change
it to xlpart if there's other stuff in those cells).

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm