Trying to batch Find and Replace Part Numbers within cells

[mastry]

Been searching around for a way to do this, but my complete lack of Excel knowledge and the seeming limited options for Office for Mac 2016 have me dead in my tracks.

One sheet has two columns to show part number supersedes.
For instance:

27241-300000,27243-300000

The other sheet has data formatted in a specific way to upload to a search system. One column is for product data, which contains all of the part number information.
For instance:

729902-01560|pos=0|qty=1|opt1=1;23876-030000|pos=1|qty=1|opt1=5;171051-01921|pos=2|qty=1|opt1=6;129900-02020|pos=3|qty=10|opt1=7;129900-02410|pos=4|qty=1|opt1=10;22351-060012|pos=5|qty=2|opt1=11;23876-030000|pos=6|qty=1|opt1=12;129900-01250|pos=7|qty=1|opt1=13;23876-030000|pos=8|qty=1|opt1=14;27241-300000|pos=9|qty=8|opt1=15;129900-02800|pos=10|qty=5|opt1=16;129900-02930|pos=11|qty=2|opt1=25;129900-01200|pos=12|qty=18|opt1=28;129902-01331|pos=13|qty=1|opt1=29|notes=UNDERSIZED PART U.S IS 0.25;129900-02340|pos=14|qty=5|opt1=30|notes=OVERSIZED PART O.S IS 0.25;129900-02940|pos=15|qty=2|opt1=33;

The few people in my office that use Excel on a daily basis are stumped thanks to the unusual data structure. Find and Replace one at a time works, but there are hundreds of part numbers to change for several documents, so I'm trying to create an automated workflow. I'm willing to use other software or resources for the process and importing the updated data back into excel as well if that helps.

Thank you for any assistance.

 

[BrianM]

mastry, Welcome to the board. I just tested this; and, based on assumptions, I think this is what you are looking for? Finding 27241-300000 in the Product Data, and replacing it with 27243-300000? You never really say exactly what it is you are tyring to do :D

https://www.mrexcel.com/forum/excel...ual-basic-applications-find-replace-loop.html

Brian

 

[FDibbins]

Not really sure exactly what you are trying to do here?
Not sure this will help, it will replace 1 specific code, but if this does work for you, we could probably put an index/match inside to find the codes, if you have more...
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][/tr]
[tr][td]

1​

[/td][td]129900-01250|pos=7|qty=1|opt1=13;23876-030000|pos=8|qty=1|opt1=14;27241-300000|pos=9|qty=8|opt1=15;[/td][td][/td][/tr]

[tr][td]

2​

[/td][td]129900-01250|pos=7|qty=1|opt1=13;23876-030000|pos=8|qty=1|opt1=14;27243-300000|pos=9|qty=8|opt1=15;[/td][td][/td][/tr]

[tr][td]

3​

[/td][td][/td][td][/td][/tr]

[tr][td]

4​

[/td][td]27241-300000[/td][td]27243-300000[/td][/tr]
[/table]
I chopped your data cell so it wouldn't be so long, but it shows the concept...
A2=SUBSTITUTE(A1,A4,B4)

 

[mastry]

Oh, dang. You're right, I didn't! But that is what I'm trying to accomplish! Sorry about that. But thank you so much Brian.

So I brought up the Visual Basic Editor, made sure the document I had was the one selected, pasted in the code from that link, updated the Sheet names (In my case the data to replace is in Sheet1 and the two columns of data defining what to replace with what is in Sheet3), and tried to run it. I just immediately get an error: "Compile error. Expected End Sub"

I imagine I'm still not quite putting this all in correctly or something. But here is that code with my two sheet changes.

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000; background-color: #fffb00}p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; background-color: #ffffff; min-height: 13.0px}p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000; background-color: #ffffff}p.p4 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000; background-color: #ffffff; min-height: 13.0px}p.p5 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #008f00; background-color: #ffffff}p.p6 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #011993; background-color: #ffffff}span.s1 {color: #011993}span.s2 {color: #000000}</style>Sub myReplace()


    Dim myDataSheet As Worksheet
    Dim myReplaceSheet As Worksheet
    Dim myLastRow As Long
    Dim myRow As Long
    Dim myFind As String
    Dim myReplace As String
    
'   Specify name of Data sheet
    Set myDataSheet = Sheets("Sheet1")
    
'   Specify name of Sheet with list of replacements
    Set myReplaceSheet = Sheets("Sheet3")
    
'   Assuming list of replacement start in column A on row 2, find last entry in list
    myLastRow = myReplaceSheet.Cells(Rows.Count, "A").End(xlUp).Row
    
    Application.ScreenUpdating = False
    
'   Loop through all list of replacments
    For myRow = 2 To myLastRow
'       Get find and replace values (from columns A and B)
        myFind = myReplaceSheet.Cells(myRow, "A")
        myReplace = myReplaceSheet.Cells(myRow, "B")
'       Start at top of data sheet and do replacements
        myDataSheet.Activate
        Range("A1").Select
'       Ignore errors that result from finding no matches
        On Error Resume Next
'       Do all replacements on column A of data sheet
        Columns("A:A").Replace What:=myFind, Replacement:=myReplace, LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
'       Reset error checking
        On Error GoTo 0
    Next myRow
    
    Application.ScreenUpdating = True
    
[COLOR=#000000][FONT=Menlo]    [/FONT][/COLOR][COLOR=#000000][FONT=Menlo]MsgBox "Replacements complete!"[/FONT][/COLOR]

Here is a screenshot of the Visual Basic Editor window as well, in case I've forgotten to do something there. This is all quite new for me. I'm used to working in Adobe software haha.

Imgur: The most awesome images on the Internet

 

[mastry]

FDibbins, I am trying to do what Brian linked. I have a sheet with 35 changes (Find A1, Replace with B1, A2>B2, etc) and I have a sheet with 17 instances of cells containing long data like that where I'm trying to find and replace the part numbers embedded within using that first sheet mentioned. This'll be something I do to a few hundred separate spreadsheets as time goes on, so coding it this way will be a tremendous help.

 

[mastry]

Okay, so I figured out that I had simply missed the "End Sub" line of the code. Added that in and it ran, giving me the "Replacements Complete" message. Problem is, it isn't actually replacing the part numbers embedded inside the cells. To better figure this out, I've moved the cells containing data I want to edit to their own sheet. Here is what I'm working with:

Sheet3

[TABLE="width: 992"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]27241-300000[/TD]
[TD]27243-300000[/TD]
[/TR]
[TR]
[TD]129900-02800[/TD]
[TD]729900-02801[/TD]
[/TR]
[TR]
[TD]129900-02930[/TD]
[TD]129900-02931[/TD]
[/TR]
[TR]
[TD]129900-02340[/TD]
[TD]129900-02341[/TD]
[/TR]
[TR]
[TD]129900-02940[/TD]
[TD]129900-02941[/TD]
[/TR]
[TR]
[TD]977770-01212[/TD]
[TD]97770-1207f[/TD]
[/TR]
[TR]
[TD]129900-32000[/TD]
[TD]129900-32001[/TD]
[/TR]
[TR]
[TD]121023-26070[/TD]
[TD]121023-26071[/TD]
[/TR]
[TR]
[TD]129001-91810[/TD]
[TD]26106-080702[/TD]
[/TR]
[TR]
[TD]129001-91810[/TD]
[TD]26106-080702[/TD]
[/TR]
[TR]
[TD]121023-01550[/TD]
[TD]121023-01551[/TD]
[/TR]
[TR]
[TD]977770-01212[/TD]
[TD]977770-1207f[/TD]
[/TR]
[TR]
[TD]22360-080000[/TD]
[TD]22372-080000[/TD]
[/TR]
[TR]
[TD]977770-01212[/TD]
[TD]977770-1207f[/TD]
[/TR]
[TR]
[TD]114250-53400[/TD]
[TD]114250-53600[/TD]
[/TR]
[TR]
[TD]129982-55600[/TD]
[TD]129982-55600-9[/TD]
[/TR]
[TR]
[TD]119000-55600[/TD]
[TD]119000-55602[/TD]
[/TR]
[TR]
[TD]129470-55610[/TD]
[TD]129470-55610-9[/TD]
[/TR]
[TR]
[TD]129915-59120[/TD]
[TD]129915-59501[/TD]
[/TR]
[TR]
[TD]129335-55700[/TD]
[TD]129335-55701[/TD]
[/TR]
[TR]
[TD]101720-55750[/TD]
[TD]124060-59380[/TD]
[/TR]
[TR]
[TD]977770-01212[/TD]
[TD]977770-1207f[/TD]
[/TR]
[TR]
[TD]129612-77710[/TD]
[TD]129900-77810[/TD]
[/TR]
[TR]
[TD]119836-77920[/TD]
[TD]129612-77760[/TD]
[/TR]
[TR]
[TD]129953-77201[/TD]
[TD]129136-77251[/TD]
[/TR]
[TR]
[TD]129900-23000[/TD]
[TD]129900-23001[/TD]
[/TR]
[TR]
[TD]129900-23910[/TD]
[TD]129900-23911[/TD]
[/TR]
[TR]
[TD]129900-23600[/TD]
[TD]129900-23601[/TD]
[/TR]
[TR]
[TD]129900-23610[/TD]
[TD]129900-23611[/TD]
[/TR]
[TR]
[TD]121254-77521[/TD]
[TD]X213062101[/TD]
[/TR]
[TR]
[TD]121254-77541[/TD]
[TD]121254-77540[/TD]
[/TR]
[TR]
[TD]121254-77590[/TD]
[TD]119129-77490[/TD]
[/TR]
[TR]
[TD]121254-77610[/TD]
[TD]119129-77210[/TD]
[/TR]
[TR]
[TD]129953-12530[/TD]
[TD]129953-12531[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 4

<style><!--table	{mso-displayed-decimal-separator:"\.";	mso-displayed-thousand-separator:"\,";}@page	{margin:.75in .7in .75in .7in;	mso-header-margin:.3in;	mso-footer-margin:.3in;}td	{padding-top:1px;	padding-right:1px;	padding-left:1px;	mso-ignore:padding;	color:black;	font-size:11.0pt;	font-weight:400;	font-style:normal;	text-decoration:none;	font-family:Calibri, sans-serif;	mso-font-charset:0;	mso-number-format:General;	text-align:general;	vertical-align:bottom;	border:none;	mso-background-source:auto;	mso-pattern:auto;	mso-protection:locked visible;	white-space:nowrap;	mso-rotate:0;}.xl63	{color:black;	font-size:12.0pt;}.xl64	{color:black;}--></style>[TABLE="width: 73"]
<!--StartFragment--> <colgroup><col width="73" style="width:55pt"> </colgroup><tbody>[TR]
  [TD="class: xl63, width: 73"]729902-01560|pos=0|qty=1|opt1=1;23876-030000|pos=1|qty=1|opt1=5;171051-01921|pos=2|qty=1|opt1=6;129900-02020|pos=3|qty=10|opt1=7;129900-02410|pos=4|qty=1|opt1=10;22351-060012|pos=5|qty=2|opt1=11;23876-030000|pos=6|qty=1|opt1=12;129900-01250|pos=7|qty=1|opt1=13;23876-030000|pos=8|qty=1|opt1=14;27241-300000|pos=9|qty=8|opt1=15;129900-02800|pos=10|qty=5|opt1=16;129900-02930|pos=11|qty=2|opt1=25;129900-01200|pos=12|qty=18|opt1=28;129902-01331|pos=13|qty=1|opt1=29|notes=UNDERSIZED  PART U.S IS 0.25;129900-02340|pos=14|qty=5|opt1=30|notes=OVERSIZED PART O.S  IS 0.25;129900-02940|pos=15|qty=2|opt1=33;[/TD]
 [/TR]
 [TR]
  [TD="class: xl64"]729900-01570|opt1=1|pos=0|qty=1;129900-01780|opt1=4|pos=1|qty=1;129951-01740|opt1=5|pos=2|qty=1;977770-01212|opt1=6|pos=3|qty=1|notes=OPTIONAL  PART;129900-01800|opt1=7|pos=4|qty=1;129900-01500|opt1=8|pos=5|qty=1;129900-32000|opt1=10|pos=6|qty=1;129900-26250|opt1=11|pos=7|qty=1;26216-100302|opt1=12|pos=8|qty=4;121023-26070|opt1=13|pos=9|qty=1;24321-000850|opt1=14|pos=10|qty=1;129900-32030|opt1=15|pos=11|qty=1;129900-32040|opt1=16|pos=12|qty=1;129100-01580|opt1=17|pos=13|qty=4;26366-100002|opt1=18|pos=14|qty=4;129900-26250|opt1=19|pos=15|qty=1;26106-080162|opt1=20|pos=16|qty=1;26106-080202|opt1=21|pos=17|qty=3;26106-080302|opt1=22|pos=18|qty=1;26106-080402|opt1=23|pos=19|qty=5;26106-080402|opt1=24|pos=20|qty=5;26106-080452|opt1=25|pos=21|qty=3;129001-91810|opt1=26|pos=22|qty=6;129001-91810|opt1=27|pos=23|qty=1;26106-100122|opt1=28|pos=24|qty=2;124160-01751|opt1=29|pos=25|qty=1;24311-000320|opt1=30|pos=26|qty=1;121023-01550|opt1=31|pos=27|qty=1;24341-000240|opt1=32|pos=28|qty=1;26106-060162|opt1=33|pos=29|qty=2;24311-000070|opt1=34|pos=30|qty=1;171031-11741|opt1=35|pos=31|qty=1;24311-000320|opt1=36|pos=32|qty=1;[/TD]
 [/TR]
 [TR]
  [TD="class: xl64"]129100-01580|opt1=1|pos=0|qty=1;129953-01620|opt1=2|pos=1|qty=1;26106-100352|opt1=5|pos=2|qty=1;26206-100702|opt1=6|pos=3|qty=1;124411-01780|opt1=7|pos=4|qty=1;129902-01710|opt1=8|pos=5|qty=1;119640-01640|opt1=9|pos=6|qty=1;22190-220002|opt1=11|pos=7|qty=1;26106-080122|opt1=12|pos=8|qty=1;977770-01212|opt1=13|pos=9|qty=1|notes=OPTIONAL  PART;[/TD]
 [/TR]
 [TR]
  [TD="class: xl64"]129916-03100|opt1=1|pos=0|qty=1;129960-03990|opt1=3|pos=1|qty=1;129910-07900|opt1=4|pos=2|qty=1;129910-07900|opt1=4-1|pos=3|qty=2;729900-11100|opt1=5|pos=4|qty=1;129150-11810|opt1=9|pos=5|qty=8|notes=REPAIR  PART;23871-005000|opt1=11|pos=6|qty=1;27241-400000|opt1=12|pos=7|qty=1;129900-11100|opt1=13|pos=8|qty=4;129900-11110|opt1=14|pos=9|qty=4;129900-11120|opt1=15|pos=10|qty=8;129795-11180|opt1=16|pos=11|qty=8;121400-11340|opt1=17|pos=12|qty=4;124460-11340|opt1=18|pos=13|qty=4;129150-11370|opt1=19|pos=14|qty=8;119625-11870|opt1=20|pos=15|qty=4;119625-11880|opt1=21|pos=16|qty=4;119625-11900|opt1=22|pos=17|qty=4;26216-060202|opt1=23|pos=18|qty=8;26366-060002|opt1=24|pos=19|qty=8;27310-080001|opt1=25|pos=20|qty=8;23876-030000|opt1=26|pos=21|qty=1;129900-11200|opt1=27|pos=22|qty=1;129900-11240|opt1=28|pos=23|qty=1;129900-11280|opt1=31|pos=24|qty=3;129900-11510|opt1=32|pos=25|qty=3;129900-11520|opt1=33|pos=26|qty=1;129900-11650|opt1=34|pos=27|qty=4;129150-11230|opt1=35|pos=28|qty=4;129150-11750|opt1=37|pos=29|qty=4;129900-11660|opt1=38|pos=30|qty=4;129150-11230|opt1=39|pos=31|qty=4;129150-11750|opt1=41|pos=32|qty=4;22242-000180|opt1=42|pos=33|qty=2;26577-040162|opt1=43|pos=34|qty=1;26366-100002|opt1=44|pos=35|qty=3;124160-11360|opt1=45|pos=36|qty=3;129910-11350|opt1=46|pos=37|qty=1;129900-11310|opt1=47|pos=38|qty=1;129900-11600|opt1=50|pos=39|qty=1;22857-500100|opt1=51|pos=40|qty=2;119100-11330|opt1=52|pos=41|qty=1;24311-000120|opt1=53|pos=42|qty=3;26106-080162|opt1=54|pos=43|qty=4;26106-100652|opt1=55|pos=44|qty=1;977770-01212|opt1=56|pos=45|qty=1|notes=OPTIONAL  PART;24311-000320|opt1=57|pos=46|qty=1;124160-01751|opt1=58|pos=47|qty=1;129953-07910|opt1=59|pos=48|qty=1;129910-03100|opt1=60|pos=49|qty=1;26106-080122|opt1=61|pos=50|qty=1;129900-11530|opt1=62|pos=51|qty=3;[/TD]
 [/TR]
 [TR]
  [TD="class: xl64"]129900-12050|opt1=1|pos=0|qty=1;129900-12100|opt1=2|pos=1|qty=1;26106-080452|opt1=4|pos=2|qty=9;121850-77021|opt1=5|pos=3|qty=1;129915-77050|opt1=6|pos=4|qty=1;26106-080352|opt1=7|pos=5|qty=4;129953-12530|opt1=8|pos=6|qty=1;119005-12571|opt1=10|pos=7|qty=1;129062-12560|opt1=11|pos=8|qty=1;129900-12530|opt1=12|pos=9|qty=1;119808-12540|opt1=13|pos=10|qty=1;119140-12680|opt1=14|pos=11|qty=1;129612-12650|opt1=15|pos=12|qty=1;23000-080000|opt1=16|pos=13|qty=2;26106-080202|opt1=17|pos=14|qty=2;129100-77910|opt1=18|pos=15|qty=1;[/TD]
 [/TR]
 [TR]
  [TD="class: xl64"]129900-13200|opt1=1|pos=0|qty=1;129900-13251|opt1=3|pos=1|qty=1;26106-080802|opt1=4|pos=2|qty=6;26216-080802|opt1=5|pos=3|qty=2;26366-080002|opt1=6|pos=4|qty=2;129400-13201|opt1=7|pos=5|qty=1;119131-18320|opt1=8|pos=6|qty=4;129953-13570|opt1=9|pos=7|qty=1;129953-13650|opt1=10|pos=8|qty=1;26106-080162|opt1=11|pos=9|qty=2;26106-080162|opt1=12|pos=10|qty=2;26366-080002|opt1=13|pos=11|qty=4;22157-080000|opt1=14|pos=12|qty=2;129400-13201|opt1=15|pos=13|qty=1;[/TD]
 [/TR]
 [TR]
  [TD="class: xl64"]129150-14200|opt1=1|pos=0|qty=8;129900-14400|opt1=2|pos=1|qty=8;129900-14580|opt1=3|pos=2|qty=1;129150-02450|opt1=4|pos=3|qty=1;129900-14100|opt1=7|pos=4|qty=1;22512-070140|opt1=8|pos=5|qty=1;26106-080162|opt1=9|pos=6|qty=2;129900-25060|opt1=10|pos=7|qty=1;129900-25100|opt1=11|pos=8|qty=1;129150-25920|opt1=14|pos=9|qty=1|notes=REPAIR  PART;26106-080552|opt1=15|pos=10|qty=2;129900-25900|opt1=16|pos=11|qty=1;129900-26000|opt1=17|pos=12|qty=1;[/TD]
 [/TR]
 [TR]
  [TD="class: xl64"]129902-21000|opt1=1|pos=0|qty=1;129900-21200|opt1=4|pos=1|qty=1;121850-21290|opt1=5|pos=2|qty=1;121850-21920|opt1=6|pos=3|qty=1;22512-070140|opt1=7|pos=4|qty=1;129900-21660|opt1=8|pos=5|qty=1;129900-21640|opt1=9|pos=6|qty=1;121850-21680|opt1=10|pos=7|qty=1;121850-21130|opt1=11|pos=8|qty=1;129953-21570|opt1=12|pos=9|qty=1;129900-21600|opt1=15|pos=10|qty=1;129902-22080|opt1=16|pos=11|qty=1;129903-22050|opt1=18|pos=12|qty=1;120130-22301|opt1=22|pos=13|qty=1;22252-000300|opt1=23|pos=14|qty=1;129900-23001|opt1=24|pos=15|qty=1;129900-23911|opt1=28|pos=16|qty=1|notes=REPAIR  PART;129900-23200|opt1=29|pos=17|qty=1;129900-23601|opt1=30|pos=18|qty=1;129900-23611|opt1=32|pos=19|qty=1|notes=UNDERSIZE  PART U.S IS 0.25;129902-22900|opt1=34|pos=20|qty=1|notes=OVERSIZE PART O.S IS  0.25;129903-22950|opt1=36|pos=21|qty=1|notes=OVERSIZE PART O.S IS 0.25;[/TD]
 [/TR]
 [TR]
  [TD="class: xl64"]129902-21000|opt1=1|pos=0|qty=1;129900-21200|opt1=4|pos=1|qty=1;121850-21290|opt1=5|pos=2|qty=1;121850-21920|opt1=6|pos=3|qty=1;22512-070140|opt1=7|pos=4|qty=1;129900-21660|opt1=8|pos=5|qty=1;129900-21640|opt1=9|pos=6|qty=1;121850-21680|opt1=10|pos=7|qty=1;121850-21130|opt1=11|pos=8|qty=6;129953-21570|opt1=12|pos=9|qty=1;129900-21600|opt1=15|pos=10|qty=1;129902-22080|opt1=16|pos=11|qty=4;129903-22050|opt1=18|pos=12|qty=4;120130-22301|opt1=22|pos=13|qty=4;22252-000300|opt1=23|pos=14|qty=8;129900-23001|opt1=24|pos=15|qty=4;129900-23911|opt1=28|pos=16|qty=4;129900-23200|opt1=29|pos=17|qty=8;129900-23601|opt1=30|pos=18|qty=4;129900-23611|opt1=32|pos=19|qty=4;129902-22900|opt1=34|pos=20|qty=4;129903-22950|opt1=36|pos=21|qty=4;[/TD]
 [/TR]
 [TR]
  [TD="class: xl64"]129900-42001|opt1=1|pos=0|qty=1;129900-42020|opt1=9|pos=1|qty=1;124465-44950|opt1=10|pos=2|qty=1;121250-44901|opt1=11|pos=3|qty=1;26106-060302|opt1=12|pos=4|qty=2;26106-080552|opt1=13|pos=5|qty=5;129900-42450|opt1=14|pos=6|qty=1;25132-004200|opt1=15|pos=7|qty=1;129900-44700|opt1=16|pos=8|qty=1;119130-44760|opt1=17|pos=9|qty=1;121550-49111|opt1=18|pos=10|qty=1;26014-080352|opt1=19|pos=11|qty=4;129916-49860|opt1=20|pos=12|qty=1;121850-49550|opt1=21|pos=13|qty=1;121850-49530|opt1=22|pos=14|qty=1;121850-49540|opt1=23|pos=15|qty=1;121850-49801|opt1=24|pos=16|qty=1;23875-030000|opt1=26|pos=17|qty=2;26106-060142|opt1=27|pos=18|qty=4;129900-49870|opt1=28|pos=19|qty=1;26106-080602|opt1=29|pos=20|qty=3;129150-49360|opt1=30|pos=21|qty=1;119266-49350|opt1=31|pos=22|qty=1;129953-44521|opt1=32|pos=23|qty=1;119259-44570|opt1=33|pos=24|qty=1;121267-44510|opt1=34|pos=25|qty=1;121450-44530|opt1=35|pos=26|qty=1;124450-44560|opt1=36|pos=27|qty=1;121267-44590|opt1=37|pos=28|qty=1;121470-44550|opt1=38|pos=29|qty=1;129150-49100|opt1=39|pos=30|qty=1;129150-49080|opt1=40|pos=31|qty=1;23000-054000|opt1=44|pos=32|qty=4;129953-44920|opt1=45|pos=33|qty=1;124722-59050|opt1=46|pos=34|qty=1;105840-59100|opt1=47|pos=35|qty=1;[/TD]
 [/TR]
 [TR]
  [TD="class: xl64"]129953-51120|opt1=1|pos=0|qty=1;[/TD]
 [/TR]
 [TR]
  [TD="class: xl64"]129900-01950|opt1=1|pos=0|qty=1;26106-100142|opt1=2|pos=1|qty=1;26106-080202|opt1=3|pos=2|qty=1;729902-53200|opt1=4|pos=3|qty=4;129902-53050|opt1=5|pos=4|qty=4;114250-53080|opt1=8|pos=5|qty=4;114250-53120|opt1=12|pos=6|qty=4;114250-53130|opt1=13|pos=7|qty=4;114250-53140|opt1=14|pos=8|qty=4;114250-53210|opt1=15|pos=9|qty=8;114250-53400|opt1=16|pos=10|qty=4;119305-59120|opt1=28|pos=11|qty=2;129150-59131|opt1=31|pos=12|qty=2;129550-59120|opt1=32|pos=13|qty=1;129150-59131|opt1=35|pos=14|qty=2;129910-59810|opt1=37|pos=15|qty=1;129910-59820|opt1=41|pos=16|qty=1;129910-59830|opt1=45|pos=17|qty=1;129910-59840|opt1=49|pos=18|qty=1;[/TD]
 [/TR]
 [TR]
  [TD="class: xl64"]129982-55600|opt1=1|pos=0|qty=1;119000-55600|opt1=2|pos=1|qty=1;129470-55610|opt1=3|pos=2|qty=1;22190-080002|opt1=4|pos=3|qty=1;129495-59300|opt1=5|pos=4|qty=1;101304-59020|opt1=6|pos=5|qty=2;103854-59191|opt1=7|pos=6|qty=1;124060-59380|opt1=9|pos=7|qty=1;22190-060002|opt1=10|pos=8|qty=1;22190-120002|opt1=11|pos=9|qty=4;23857-060000|opt1=12|pos=10|qty=1;26106-080102|opt1=13|pos=11|qty=1;129980-59530|opt1=14|pos=12|qty=1;22190-140002|opt1=15|pos=13|qty=2;26106-100252|opt1=16|pos=14|qty=2;124066-59100|opt1=17|pos=15|qty=7;129957-59040|opt1=18|pos=16|qty=1;129335-59090|opt1=19|pos=17|qty=1;119560-59110|opt1=20|pos=18|qty=1;129950-59311|opt1=21|pos=19|qty=1;105025-59580|opt1=22|pos=20|qty=1;124766-59050|opt1=23|pos=21|qty=2;129961-59110|opt1=24|pos=22|qty=1;121855-59880|opt1=25|pos=23|qty=1;121250-59550|opt1=26|pos=24|qty=3;129915-59500|opt1=27|pos=25|qty=1;124066-59100|opt1=28|pos=26|qty=2;129915-59120|opt1=29|pos=27|qty=1;128300-59910|opt1=30|pos=28|qty=1;22190-140002|opt1=36|pos=29|qty=2;121250-49110|opt1=37|pos=30|qty=1;129957-59040|opt1=38|pos=31|qty=1;119560-59110|opt1=39|pos=32|qty=1;129335-59090|opt1=40|pos=33|qty=1;121750-59890|opt1=41|pos=34|qty=1;124766-59050|opt1=42|pos=35|qty=4;121855-59880|opt1=43|pos=36|qty=1;129150-59580|opt1=44|pos=37|qty=1;121855-59880|opt1=45|pos=38|qty=1;124766-59050|opt1=46|pos=39|qty=2;129335-55700|opt1=47|pos=40|qty=1;104200-55321|opt1=48|pos=41|qty=1;104200-55330|opt1=49|pos=42|qty=1;104200-55341|opt1=50|pos=43|qty=1;124064-55510|opt1=51|pos=44|qty=1;102103-55520|opt1=52|pos=45|qty=1;110250-55610|opt1=53|pos=46|qty=1;121257-55720|opt1=54|pos=47|qty=1;121257-55731|opt1=55|pos=48|qty=1;101720-55750|opt1=57|pos=49|qty=2;104200-55750|opt1=58|pos=50|qty=1;171081-55910|opt1=59|pos=51|qty=1;23418-060000|opt1=60|pos=52|qty=2;24311-240100|opt1=61|pos=53|qty=1;129322-77401|opt1=62|pos=54|qty=1;[/TD]
 [/TR]
 [TR]
  [TD="class: xl64"]129900-66790|opt1=1|pos=0|qty=1;129900-66830|opt1=3|pos=1|qty=1;129566-66880|opt1=4|pos=2|qty=1;129566-66890|opt1=5|pos=3|qty=1;119840-66811|opt1=6|pos=4|qty=1;129900-66820|opt1=7|pos=5|qty=1;22117-080000|opt1=8|pos=6|qty=1;22360-080000|opt1=9|pos=7|qty=1;26106-080122|opt1=10|pos=8|qty=2;26106-080122|opt1=11|pos=9|qty=2;26116-050102|opt1=12|pos=10|qty=3;[/TD]
 [/TR]
 [TR]
  [TD="class: xl64"]129953-77019|opt1=1|pos=0|qty=1;121254-77402|opt1=2|pos=1|qty=1;121254-77430|opt1=5|pos=2|qty=1;121254-77480|opt1=9|pos=3|qty=1;121254-77521|opt1=13|pos=4|qty=1;121254-77541|opt1=14|pos=5|qty=2;121254-77550|opt1=15|pos=6|qty=1;121254-77571|opt1=16|pos=7|qty=1;121254-77581|opt1=17|pos=8|qty=2;121254-77590|opt1=18|pos=9|qty=4;121254-77600|opt1=19|pos=10|qty=1;121254-77610|opt1=21|pos=11|qty=1;121254-77640|opt1=23|pos=12|qty=1;129953-77670|opt1=25|pos=13|qty=1;121254-77681|opt1=26|pos=14|qty=1;121254-77690|opt1=27|pos=15|qty=1;121254-77700|opt1=28|pos=16|qty=1;121254-77730|opt1=31|pos=17|qty=1;121254-77770|opt1=35|pos=18|qty=2;121254-77790|opt1=36|pos=19|qty=2;121254-77810|opt1=37|pos=20|qty=1;26106-060302|opt1=38|pos=21|qty=3;26106-060452|opt1=39|pos=22|qty=2;121254-77780|opt1=40|pos=23|qty=2;129953-77660|opt1=41|pos=24|qty=1;26116-120302|opt1=42|pos=25|qty=2;124550-77030|opt1=43|pos=26|qty=1;[/TD]
 [/TR]
 [TR]
  [TD="class: xl64"]119836-77201|opt1=1|pos=0|qty=1;129612-77700|opt1=2|pos=1|qty=1;129612-77710|opt1=3|pos=2|qty=1;119836-77920|opt1=4|pos=3|qty=1;129612-77730|opt1=5|pos=4|qty=1;129612-77740|opt1=6|pos=5|qty=1;129612-77750|opt1=7|pos=6|qty=1;129150-77340|opt1=8|pos=7|qty=1;129953-77201|opt1=9|pos=8|qty=1;129612-77260|opt1=10|pos=9|qty=1;129953-77330|opt1=11|pos=10|qty=1;26014-080252|opt1=12|pos=11|qty=1;129136-77100|opt1=13|pos=12|qty=1;106500-44810|opt1=14|pos=13|qty=1;119810-77340|opt1=15|pos=14|qty=1;128300-77920|opt1=16|pos=15|qty=1;[/TD]
 [/TR]
 [TR]
  [TD="class: xl64"]729902-92740|opt1=1|pos=0|qty=1;129902-01331|opt1=2|pos=1|qty=1;24311-240100|opt1=3|pos=2|qty=1;124460-11340|opt1=4|pos=3|qty=4;119625-11870|opt1=5|pos=4|qty=4;119625-11880|opt1=6|pos=5|qty=4;129900-11310|opt1=7|pos=6|qty=1;129900-12050|opt1=8|pos=7|qty=1;129900-13251|opt1=9|pos=8|qty=1;23414-080000|opt1=10|pos=9|qty=2;121400-11340|opt1=11|pos=10|qty=4;129900-32030|opt1=12|pos=11|qty=2;129900-32040|opt1=13|pos=12|qty=1;129900-42020|opt1=14|pos=13|qty=1;129900-49870|opt1=15|pos=14|qty=1;121850-49550|opt1=16|pos=15|qty=1;121850-77021|opt1=17|pos=16|qty=1;121850-49540|opt1=18|pos=17|qty=1;24311-000070|opt1=19|pos=18|qty=1;24311-000120|opt1=20|pos=19|qty=3;24341-000240|opt1=21|pos=20|qty=1;24311-000320|opt1=22|pos=21|qty=3;129400-13201|opt1=23|pos=22|qty=2;22190-080002|opt1=24|pos=23|qty=4;129150-49360|opt1=25|pos=24|qty=1;22190-140002|opt1=26|pos=25|qty=2;24321-000850|opt1=27|pos=26|qty=1;22190-220002|opt1=28|pos=27|qty=1;124465-44950|opt1=29|pos=28|qty=1;129400-92450|opt1=30|pos=29|qty=1;977770-01212|opt1=31|pos=30|qty=1;[/TD]
 [/TR]
<!--EndFragment--></tbody>[/TABLE]

 

[mastry]

And I neglected to mention that I changed the Sheet names in the VBA code when I created the new sheet. And it is Sheet4, not Sheet 4

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #011993; background-color: #ffffff}p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; background-color: #ffffff; min-height: 13.0px}p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000; background-color: #ffffff}p.p4 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000; background-color: #ffffff; min-height: 13.0px}p.p5 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #008f00; background-color: #ffffff}span.s1 {color: #011993}span.s2 {color: #000000}</style>Sub myReplace()


    Dim myDataSheet As Worksheet
    Dim myReplaceSheet As Worksheet
    Dim myLastRow As Long
    Dim myRow As Long
    Dim myFind As String
    Dim myReplace As String
    
'   Specify name of Data sheet
    Set myDataSheet = Sheets("Sheet4")
    
'   Specify name of Sheet with list of replacements
    Set myReplaceSheet = Sheets("Sheet3")
    
'   Assuming list of replacement start in column A on row 2, find last entry in list
    myLastRow = myReplaceSheet.Cells(Rows.Count, "A").End(xlUp).Row
    
    Application.ScreenUpdating = False
    
'   Loop through all list of replacments
    For myRow = 2 To myLastRow
'       Get find and replace values (from columns A and B)
        myFind = myReplaceSheet.Cells(myRow, "A")
        myReplace = myReplaceSheet.Cells(myRow, "B")
'       Start at top of data sheet and do replacements
        myDataSheet.Activate
        Range("A1").Select
'       Ignore errors that result from finding no matches
        On Error Resume Next
'       Do all replacements on column A of data sheet
        Columns("A:A").Replace What:=myFind, Replacement:=myReplace, LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
'       Reset error checking
        On Error GoTo 0
    Next myRow
    
    Application.ScreenUpdating = True
    
    MsgBox "Replacements complete!"


[COLOR=#011993][FONT=Menlo]End[/FONT][/COLOR][FONT=Menlo] [/FONT][COLOR=#011993][FONT=Menlo]Sub[/FONT][/COLOR]

 

[mastry]

Please excuse the multiple postings. But I did some further testing, and the script works as expected on Windows, making the expected replacements. Just won't do them on my Mac...

 

[BrianM]

mastry,
Since I don't work on a mac, I don't think I can be of further assistance. Perhaps you could contact Joe4 for help; or, create a new thread with the heading "Converting Windows based macro to Mac.

Brian

 

[mastry]

Luckily I can commandeer some Windows machines if necessary later on. But, again, thank you sooooo much for linking me that code, Brian. You're a lifesaver.