Hi,
Here is a VLOOKUP version. It assumes the original locations are in
column A and a table of Old/New locations in colums M and N - M contains the
old and N the corresponding new. Change thse ranges as required.

Sub LookupLocations()

Dim i As Long, iLastrow As Long, rngA As Range, cell As Range, res As Variant
Dim LookUptbl As Range

iLastrow = Cells(Rows.Count, "A").End(xlUp).Row
' Changes these as required
Set rngA = Range("a2:a" & iLastrow) ' Source Locations
Set LookUptbl = Range("M1:N200") ' contains Old/New locations

For Each cell In rngA
res = Application.VLookup(cell.Value, LookUptbl, 2, False)
If Not IsError(res) Then
cell.Value = res ' replace old with new
Else
MsgBox cell.Value & " not found in lookup table"
End If
Next cell

End Sub

"Guillermo" wrote:

sorry, I tried it but it did not work, it returns some weird numbers like it
tries to replace every time number it finds

like 2222

it replaces with 234434 234434 234434 etc..

som eone toldme to to use anf if statement and then vlook up any ideas?
"Toppers" wrote in message
...
Hi,
This replaces old values with new, assuming data to be changed is
in column A. Change column as required.

Sub ReplaceLocations()

Dim i As Long, OldLocation As Variant, NewLocation As Variant

OldLocation = Array(1234, 6789, 5643) ' Old Location Numbers
NewLocation = Array(5321, 9065, 7112) ' New Location Numbers

For i = LBound(OldLocation) To UBound(OldLocation)
Columns("A:A").Replace What:=OldLocation(i),
Replacement:=NewLocation(i)
Next i

End Sub


HTH

"Guillermo" wrote:

I have a file with location numbers, the company i work for has a
differetn
location number than those.

here is the request, I need to take the original number and have it
change
to our number, does this make sense.?