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.?

I tried the array, but it has a problem it guives some crazy numbers, not
the correct store #'s, this is basically find and replace, like the guy was
saying but it needs to be programatically,

Does the array need to be in numerical order (1,2,3,4...)?

can it hold more than 100 items?

should i put it in a command button?

"Guillermo" wrote in message
.. .
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.?

yes but there are 100 independet values
"Paul Martin" wrote in message
ups.com...
Guillermo

This sounds like a simple find and replace, assuming that you can map
one number to another.

Regards

Paul Martin
Melbourne, Australia

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.?

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.?