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

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 ?



"Steve Vincent" wrote:

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

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?


"Mankind" wrote:
Q. Does the INVENTory system represents the collection of *old orders or
*old deliveries ?



"Steve Vincent" wrote:

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

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


"Mankind" wrote:
Q. Does the INVENTory system represents the collection of *old orders or
*old deliveries ?



"Steve Vincent" wrote:

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

1. Yes
2. Yes
and
3. Yes


"Zone" wrote:

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


"Mankind" wrote:
Q. Does the INVENTory system represents the collection of *old orders or
*old deliveries ?



"Steve Vincent" wrote:

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

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
"Steve Vincent" wrote in message
...
1. Yes
2. Yes
and
3. Yes


"Zone" wrote:

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


"Mankind" wrote:
Q. Does the INVENTory system represents the collection of *old orders
or
*old deliveries ?



"Steve Vincent" wrote:

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

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


"Zone" wrote in message
...
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
"Steve Vincent" wrote in message
...
1. Yes
2. Yes
and
3. Yes


"Zone" wrote:

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


"Mankind" wrote:
Q. Does the INVENTory system represents the collection of *old orders
or
*old deliveries ?



"Steve Vincent" wrote:

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

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

"Zone" wrote:

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


"Zone" wrote in message
...
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
"Steve Vincent" wrote in message
...
1. Yes
2. Yes
and
3. Yes


"Zone" wrote:

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


"Mankind" wrote:
Q. Does the INVENTory system represents the collection of *old orders
or
*old deliveries ?



"Steve Vincent" wrote:

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

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


"Steve Vincent" wrote:

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

"Zone" wrote:

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


"Zone" wrote in message
...
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
"Steve Vincent" wrote in message
...
1. Yes
2. Yes
and
3. Yes


"Zone" wrote:

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


"Mankind" wrote:
Q. Does the INVENTory system represents the collection of *old orders
or
*old deliveries ?



"Steve Vincent" wrote:

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

Steve,
Great! Thanks for letting me know.
James
"Steve Vincent" wrote in message
...
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


"Steve Vincent" wrote:

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

"Zone" wrote:

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


"Zone" wrote in message
...
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
"Steve Vincent" wrote in
message
...
1. Yes
2. Yes
and
3. Yes


"Zone" wrote:

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


"Mankind" wrote:
Q. Does the INVENTory system represents the collection of *old
orders
or
*old deliveries ?



"Steve Vincent" wrote:

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