Find/Replace hundreds of values

[Guest]

I need to replace hundreds of part numbers with new part numbers (migrating
to a new inventory system). These part numbers appear on about 120 different
Excel workbooks, each of which is a different order form. Any single order
form/workbook could contain 40 to 100 of the part numbers on the form. These
are usually in contiguous ranges, usually columns, but sometimes more than
one column on a spreadsheet. So, in other words, the layout is not
consistent from workbook/form to workbook/form.

I am looking for a way to replace all of the old part numbers with the new
part numbers. I already have a table of the old part numbers with the new
part numbers next to them. In a simpler scenario, I can envision using
HLOOKUP or VLOOKUP to find the number, but I don't know how to (1) replace
the existing number, or (2) continuously search the workbook for the old part
numbers and replace them with the new part numbers until it finds them all.

Any ideas of where to start with this? Thank you very much in advance.

Steve Vincent
(e-mail address removed)

 

[Guest]

Any ideas of where to start with this? Thank you very much in
advance.sorry if i dont get it exactly...

1. the source 120 workbooks are *ORDER* form.
2. you're migrating to a new inventory system.

Q. Does the INVENTory system represents the collection of *old orders or
*old deliveries ?

 

[Guest]

The main issue here is: the new inventory system is requiring us to change
to a new item number system for all of our items, and I just need to replace
the old item number throughout around 120 workbooks (each one is an order
form) with the new item numbers. Each of these 120 forms (each on a separate
workbook) contains from 40 to 100 item numbers on it.

Does that make sense to you now?

 

[Zone]

Steve, Still need help with this? If so,
1. Are all the worksheets that need changing located in one directory?
2. Do they all have an .xls extension?
3. Do they all have only one worksheet, or if they have more than one
worksheet, is the data that needs changing on the same worksheet in all of
them?
James

 

[Guest]

1. Yes
2. Yes
and
3. Yes

Steve, Still need help with this? If so,
1. Are all the worksheets that need changing located in one directory?
2. Do they all have an .xls extension?
3. Do they all have only one worksheet, or if they have more than one
worksheet, is the data that needs changing on the same worksheet in all of
them?
James

 

[Zone]

Ok, I'll get back to you. In the meantime, I'd make sure you have a backup
of the directory. Since you'll be changing and resaving lots of files, an
error could occur, so good idea to have a backup first. James

 

[Zone]

Steve,
Open a new workbook. On the first worksheet of the workbook, put the word
Old in A1 and the word New in B1. Put your list of old part numbers in A,
beginning in A2. Put your new part numbers in B, beginning in B2. When
finished, you should have a complete list of part numbers in A, with the
replacement part number for each on the corresponding row in B. Do not
leave gaps in the list. Do not put anything under the list. Save the file
in a different folder than the files to be changed.

Copy the code below. Insert a new standard module in the workbook and paste
the code in there. Change the myPath constant to the complete path of the
folder where the files are located, ending with a backslash. Return to the
worksheet view. Save again and run the sub NewPartsNums. It will change
all the part numbers on the first worksheet in each workbook, then close and
save each workbook. If the worksheet is protected, it will not be changed.
Hope this works for you! James

Const MYPATH = "c:\Parts Folder\"

Sub NewPartsNums()
Dim p As Long, f As Long, myOld, myNew, myFile
ListFiles
With ThisWorkbook.Worksheets(1)
For f = 1 To .Cells(Rows.Count, "g").End(xlUp).Row
myFile = .Cells(f, "g")
Workbooks.Open MYPATH & myFile
ActiveWorkbook.Worksheets(1).Activate
If Not ActiveSheet.ProtectContents Then
For p = 2 To .Cells(Rows.Count, "a").End(xlUp).Row
myOld = .Cells(p, "a")
myNew = .Cells(p, "b")
Cells.Replace what:=myOld, replacement:=myNew, _
lookat:=xlWhole
Next p
Workbooks(myFile).Close savechanges:=True
End If
.Cells(f, "f") = "X"
Next f
End With
End Sub

Sub ListFiles()
Dim PutRow As Long, fName As String
PutRow = 1
Columns("g").Clear
fName = Dir(MYPATH & "*.xls")
Cells(PutRow, "g") = fName
PutRow = PutRow + 1
Do
fName = Dir
Cells(PutRow, "g") = fName
PutRow = PutRow + 1
Loop Until fName = ""
End Sub

 

[Guest]

James,
Thank you! I look forward to trying this out first opportunity next week! I
will let you know how it goes. Have a great weekend,
Steve

 

[Guest]

James,

It worked like a charm, as advertised! I can't thank you enough for your
expert advice. It will save us many hours of manual labor.

Many thanks,

Steve Vincent
(e-mail address removed)

 

[Zone]

Steve,
Great! Thanks for letting me know.
James

James,

It worked like a charm, as advertised! I can't thank you enough for your
expert advice. It will save us many hours of manual labor.

Many thanks,

Steve Vincent
(e-mail address removed)