Find and Replace/Look up
 

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.

:confused:


--
Mr_Philip
------------------------------------------------------------------------
Mr_Philip's Profile: http://www.excelforum.com/member.php...o&userid=31415
View this thread: http://www.excelforum.com/showthread...hreadid=523119

Find and Replace/Look up
 

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

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

fast


--
Portuga
------------------------------------------------------------------------
Portuga's Profile: http://www.excelforum.com/member.php...fo&userid=6385
View this thread: http://www.excelforum.com/showthread...hreadid=523119

Find and Replace/Look up
 

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

Mr_Philip wrote:

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.

:confused:

--
Mr_Philip
------------------------------------------------------------------------
Mr_Philip's Profile: http://www.excelforum.com/member.php...o&userid=31415
View this thread: http://www.excelforum.com/showthread...hreadid=523119

--

Dave Peterson