Hello I'm new here. I finished a formula that uses nested IF statements along with SUMPRODUCT, --ISNUMBER, and SEARCH, in order to put a string in a cell if found match in cell $F5 (and so on). The problem is when I attempt to create MACRO. Before starting MACRO, I copy formula then paste into Notepad. Then I start recording MACRO and copy from notepad (incase if not in buffer) then paste directly into cell V5. If I do so in one big formula, MACRO records successfully, but after run MACRO gives error.
In Cell V5
=IF((SUMPRODUCT(--ISNUMBER(SEARCH({"201","402","603","612","804","805","1206","1210","1217","2010","2020","2045","2220","2312","2512","2824","2413","7343-44","D Case","D-CASE","DCASE","SMD","SSMINI2","SOT","SOD","SOIC","SMT","1411","1005","1608","2012","3216","3225","3246","5025","6332","0102","0204","0207","1812","SOP","QFP"," BGA","FBGA","LBGA","PBGA","CBGA","OBGA","MAPBGA","MAP-BGA","UBGA","TBGA","SBGA","PLCC","QFN","DFN","FPGA","2917","DO214","DO-214","8SO","8-SO","DPAK","D-PAK","SC-70","SC70","SC74","SC74","SC-88","SC88","TSOT","T-SOT","T-SOC","TSOC","US-8","US8","SO-8","SO8","SO14","SO16","SO18","SO20","SO24","SO28","SO32","SO-","SOM14","SOM16","16-SOL","16SOL","SOM18","SOM20","SOM24","SOM28","SOM32","SOM-","10SON","10-SON","VSON","V-SON","LFCSP","LPQ","LMP","14SO","14-SO","IND","D2-PAK","D2PAK","CHIP","CERAMIC","SM-8","SM8","SOJ","TO-PMOD","TOPMOD","TO263","TO-263","TO236","TO-236","VINCU","CGA","CERPACK","CER-PACK","QGP","LLP","LGA","LTCC","MCM","MICRO SMD","MICROSMD","MICRO SMT","MICROSMT","LCC","CFP","SIDEBRAZE","SIDE BRAZE","UMAX","U-MAX","WSON","W-SON","CSP","DSP","COF","COB","SMA","SMC"},$B2)))>0)=TRUE,"SMT",IF((SUMPRODUCT(--ISNUMBER(SEARCH({"T/H","TH ","DIFF","RECEPT","BASE-T","BASET","RCPT","POS","HDR","HEADER","PIN","SW","SWITCH","PIP","AXIAL","RAD","RELAY","2-AG","2AG","MICROAB","MICRO-AB","MICRO AB","SIP","DIP","RECPT","TO-247","TO247","TO92","TO-92","TO-220","TO220","TO-3","TO3","TO-5","TO5","TO-18","TO18","TO-39","TO39","TO-46","TO46","TO-66","TO66","TO-99","TO99","TO-100","TO100","TO-226","TO226","TO-251","TO251","TO-252","TO252","TO-262","TO262","TO-263","TO263","TO-274","TO274","TACT","REED","JUMPER","JMP","JP","VARIS","VARA","USB","MOD","ELECTROLYTIC","CONN","RESON","IC","SOCK","QIP","QIL","ZIP","OPGA","FCPGA","PAC","PGA","HOLDER","HEADER","CARBON FILM","CARBFILM","CARB FILM","METAL OXIDE","METAL FILM","POTENTIOMETER","POT ","TRIMMER","METALLIZED","MET ","MET-","MEA ","MEA-","CAP FILM MKT","CAP FILM","METAL POLY"},$B2)))>0)=TRUE,"TH",IF((SUMPRODUCT(--ISNUMBER(SEARCH({"SCREW","SCR","SC ","NUT","WASHER","WS","INSULATION","INSUL","HEAT SHRINK","HEATSHRINK","HT SHRINK","HTSHRINK","HEATSINK","HEAT SINK","GLUE","APOXY","EPOXY","BRACKET","BRACK","WIRE","WR","CABLE","CBL","TERMINAL","TERM","CRIMP","CMP","JACK","JCK","JK","BATT","TUBE","LOCKBAR","LOCK BAR","CARBON","ZINC","ENCLOS","STAINLESS STEEL","BRACE","FRAME","BLOCK","SPRING","PLATE","STRAIN REL","LID","BUMPER","BUZZER","CLIP","SPACER","LABEL","STRAP","BOOT","COVER","HEX","PHILIPS","PHILLIPS","TUBE","ADHESIVE","ADHES","TUBING","FASTENER","FASTNER","PLUNGER","PLUNG","CHASSIS","RUBBER","ALUM","RAIL","FUSEHLDR","KEYBOARD","THERM PAD","THERMAL PAD","DISPLAY","DISP","KNOB","LATCH","HOUSING","HOUSEING","AWG","PLUG","PUTTY","ENDCAP","END CAP","STANDOFF","STAND-OFF","STAND OFF","GROMET","GROMMET","CABLE TIE","CBL TIE","CB TIE","SPLICE","GASK","BUTTON","BUT","CAP","ORING","O-RING","TOUCHSCREEN","TOUCH SCREEN","0-80","1-64","1-72","2-56","2-64","3-48","3-56","4-40","4-48","5-40","5-44","6-32","6-40","8-32","8-36","10-24","10-32","12-24","12-28","CONN RING","CONN QC","SPEAKER","SPKR"},$B2)))>0)=TRUE,"MECH","")))
Next I tried to substitute long substring in SEARCH command with cell for each of the three SEARCH formulas like this:
In Cell V5
=IF((SUMPRODUCT(--ISNUMBER(SEARCH({$X$5},$F5)))>0)=TRUE,"SMT",IF((SUMPRODUCT(--ISNUMBER(SEARCH({$Y$5},$F5)))>0)=TRUE,"TH",IF((SUMPRODUCT(--ISNUMBER(SEARCH({$Z$5},$F5)))>0)=TRUE,"MECH","")))
In Cell X5
"201","402","603","612","804","805","1206","1210","1217","2010","2020","2045","2220","2312","2512","2824","2413","7343-44","D Case","D-CASE","DCASE","SMD","SSMINI2","SOT","SOD","SOIC","SMT","1411","1005","1608","2012","3216","3225","3246","5025","6332","0102","0204","0207","1812","SOP","QFP"," BGA","FBGA","LBGA","PBGA","CBGA","OBGA","MAPBGA","MAP-BGA","UBGA","TBGA","SBGA","PLCC","QFN","DFN","FPGA","2917","DO214","DO-214","8SO","8-SO","DPAK","D-PAK","SC-70","SC70","SC74","SC74","SC-88","SC88","TSOT","T-SOT","T-SOC","TSOC","US-8","US8","SO-8","SO8","SO14","SO16","SO18","SO20","SO24","SO28","SO32","SOM-,"SOM14","SOM16","16-SOL","16SOL","SOM18","SOM20","SOM24","SOM28","SOM32","SOM-","10SON","10-SON","VSON","V-SON","LFCSP","LPQ","LMP","14SO","14-SO","IND","D2-PAK","D2PAK","CHIP","CERAMIC","SM-8","SM8","SOJ","TO-PMOD","TOPMOD","TO263","TO-263","TO236","TO-236","VINCU","CGA","CERPACK","CER-PACK","QGP","LLP","LGA","LTCC","MCM","MICRO SMD","MICROSMD","MICRO SMT","MICROSMT","LCC","CFP","SIDEBRAZE","SIDE BRAZE","UMAX","U-MAX","WSON","W-SON","CSP","DSP","COF","COB","SMA","SMC"
In Cell Y5
"T/H","TH ","DIFF","RECEPT","BASE-T","BASET","RCPT","POS","HDR","HEADER","PIN","SW","SWITCH","PIP","AXIAL","RAD","RELAY","2-AG","2AG","MICROAB","MICRO-AB","MICRO AB","SIP","DIP","RECPT","TO-247","TO247","TO92","TO-92","TO-220","TO220","TO-3","TO3","TO-5","TO5","TO-18","TO18","TO-39","TO39","TO-46","TO46","TO-66","TO66","TO-99","TO99","TO-100","TO100","TO-226","TO226","TO-251","TO251","TO-252","TO252","TO-262","TO262","TO-263","TO263","TO-274","TO274","TACT","REED","JUMPER","JMP","JP","VARIS","VARA","USB","MOD","ELECTROLYTIC","CONN","RESON","IC","SOCK","QIP","QIL","ZIP","OPGA","FCPGA","PAC","PGA","HOLDER","HEADER","CARBON FILM","CARBFILM","CARB FILM","METAL OXIDE","METAL FILM","POTENTIOMETER","POT ","TRIMMER","METALLIZED","MET ","MET-","MEA ","MEA-","CAP FILM MKT","CAP FILM","METAL POLY"
In Cell Z5
"SCREW","SCR","SC ","NUT","WASHER","WS","INSULATION","INSUL","HEAT SHRINK","HEATSHRINK","HT SHRINK","HTSHRINK","HEATSINK","HEAT SINK","GLUE","APOXY","EPOXY","BRACKET","BRACK","WIRE","WR","CABLE","CBL","TERMINAL","TERM","CRIMP","CMP","JACK","JCK","JK","BATT","TUBE","LOCKBAR","LOCK BAR","CARBON","ZINC","ENCLOS","STAINLESS STEEL","BRACE","FRAME","BLOCK","SPRING","PLATE","STRAIN REL","LID","BUMPER","BUZZER","CLIP","SPACER","LABEL","STRAP","BOOT","COVER","HEX","PHILIPS","PHILLIPS","TUBE","ADHESIVE","ADHES","TUBING","FASTENER","FASTNER","PLUNGER","PLUNG","CHASSIS","RUBBER","ALUM","RAIL","FUSEHLDR","KEYBOARD","THERM PAD","THERMAL PAD","DISPLAY","DISP","KNOB","LATCH","HOUSING","HOUSEING","AWG","PLUG","PUTTY","ENDCAP","END CAP","STANDOFF","STAND-OFF","STAND OFF","GROMET","GROMMET","CABLE TIE","CBL TIE","CB TIE","SPLICE","GASK","BUTTON","BUT","CAP","ORING","O-RING","TOUCHSCREEN","TOUCH SCREEN","0-80","1-64","1-72","2-56","2-64","3-48","3-56","4-40","4-48","5-40","5-44","6-32","6-40","8-32","8-36","10-24","10-32","12-24","12-28","CONN RING","CONN QC","SPEAKER","SPKR"
Now I get error in SEARCH formulas --> ex. SEARCH({$X$5},$F5)
Formula alone without MACRO works great. Please help.
In Cell V5
=IF((SUMPRODUCT(--ISNUMBER(SEARCH({"201","402","603","612","804","805","1206","1210","1217","2010","2020","2045","2220","2312","2512","2824","2413","7343-44","D Case","D-CASE","DCASE","SMD","SSMINI2","SOT","SOD","SOIC","SMT","1411","1005","1608","2012","3216","3225","3246","5025","6332","0102","0204","0207","1812","SOP","QFP"," BGA","FBGA","LBGA","PBGA","CBGA","OBGA","MAPBGA","MAP-BGA","UBGA","TBGA","SBGA","PLCC","QFN","DFN","FPGA","2917","DO214","DO-214","8SO","8-SO","DPAK","D-PAK","SC-70","SC70","SC74","SC74","SC-88","SC88","TSOT","T-SOT","T-SOC","TSOC","US-8","US8","SO-8","SO8","SO14","SO16","SO18","SO20","SO24","SO28","SO32","SO-","SOM14","SOM16","16-SOL","16SOL","SOM18","SOM20","SOM24","SOM28","SOM32","SOM-","10SON","10-SON","VSON","V-SON","LFCSP","LPQ","LMP","14SO","14-SO","IND","D2-PAK","D2PAK","CHIP","CERAMIC","SM-8","SM8","SOJ","TO-PMOD","TOPMOD","TO263","TO-263","TO236","TO-236","VINCU","CGA","CERPACK","CER-PACK","QGP","LLP","LGA","LTCC","MCM","MICRO SMD","MICROSMD","MICRO SMT","MICROSMT","LCC","CFP","SIDEBRAZE","SIDE BRAZE","UMAX","U-MAX","WSON","W-SON","CSP","DSP","COF","COB","SMA","SMC"},$B2)))>0)=TRUE,"SMT",IF((SUMPRODUCT(--ISNUMBER(SEARCH({"T/H","TH ","DIFF","RECEPT","BASE-T","BASET","RCPT","POS","HDR","HEADER","PIN","SW","SWITCH","PIP","AXIAL","RAD","RELAY","2-AG","2AG","MICROAB","MICRO-AB","MICRO AB","SIP","DIP","RECPT","TO-247","TO247","TO92","TO-92","TO-220","TO220","TO-3","TO3","TO-5","TO5","TO-18","TO18","TO-39","TO39","TO-46","TO46","TO-66","TO66","TO-99","TO99","TO-100","TO100","TO-226","TO226","TO-251","TO251","TO-252","TO252","TO-262","TO262","TO-263","TO263","TO-274","TO274","TACT","REED","JUMPER","JMP","JP","VARIS","VARA","USB","MOD","ELECTROLYTIC","CONN","RESON","IC","SOCK","QIP","QIL","ZIP","OPGA","FCPGA","PAC","PGA","HOLDER","HEADER","CARBON FILM","CARBFILM","CARB FILM","METAL OXIDE","METAL FILM","POTENTIOMETER","POT ","TRIMMER","METALLIZED","MET ","MET-","MEA ","MEA-","CAP FILM MKT","CAP FILM","METAL POLY"},$B2)))>0)=TRUE,"TH",IF((SUMPRODUCT(--ISNUMBER(SEARCH({"SCREW","SCR","SC ","NUT","WASHER","WS","INSULATION","INSUL","HEAT SHRINK","HEATSHRINK","HT SHRINK","HTSHRINK","HEATSINK","HEAT SINK","GLUE","APOXY","EPOXY","BRACKET","BRACK","WIRE","WR","CABLE","CBL","TERMINAL","TERM","CRIMP","CMP","JACK","JCK","JK","BATT","TUBE","LOCKBAR","LOCK BAR","CARBON","ZINC","ENCLOS","STAINLESS STEEL","BRACE","FRAME","BLOCK","SPRING","PLATE","STRAIN REL","LID","BUMPER","BUZZER","CLIP","SPACER","LABEL","STRAP","BOOT","COVER","HEX","PHILIPS","PHILLIPS","TUBE","ADHESIVE","ADHES","TUBING","FASTENER","FASTNER","PLUNGER","PLUNG","CHASSIS","RUBBER","ALUM","RAIL","FUSEHLDR","KEYBOARD","THERM PAD","THERMAL PAD","DISPLAY","DISP","KNOB","LATCH","HOUSING","HOUSEING","AWG","PLUG","PUTTY","ENDCAP","END CAP","STANDOFF","STAND-OFF","STAND OFF","GROMET","GROMMET","CABLE TIE","CBL TIE","CB TIE","SPLICE","GASK","BUTTON","BUT","CAP","ORING","O-RING","TOUCHSCREEN","TOUCH SCREEN","0-80","1-64","1-72","2-56","2-64","3-48","3-56","4-40","4-48","5-40","5-44","6-32","6-40","8-32","8-36","10-24","10-32","12-24","12-28","CONN RING","CONN QC","SPEAKER","SPKR"},$B2)))>0)=TRUE,"MECH","")))
Next I tried to substitute long substring in SEARCH command with cell for each of the three SEARCH formulas like this:
In Cell V5
=IF((SUMPRODUCT(--ISNUMBER(SEARCH({$X$5},$F5)))>0)=TRUE,"SMT",IF((SUMPRODUCT(--ISNUMBER(SEARCH({$Y$5},$F5)))>0)=TRUE,"TH",IF((SUMPRODUCT(--ISNUMBER(SEARCH({$Z$5},$F5)))>0)=TRUE,"MECH","")))
In Cell X5
"201","402","603","612","804","805","1206","1210","1217","2010","2020","2045","2220","2312","2512","2824","2413","7343-44","D Case","D-CASE","DCASE","SMD","SSMINI2","SOT","SOD","SOIC","SMT","1411","1005","1608","2012","3216","3225","3246","5025","6332","0102","0204","0207","1812","SOP","QFP"," BGA","FBGA","LBGA","PBGA","CBGA","OBGA","MAPBGA","MAP-BGA","UBGA","TBGA","SBGA","PLCC","QFN","DFN","FPGA","2917","DO214","DO-214","8SO","8-SO","DPAK","D-PAK","SC-70","SC70","SC74","SC74","SC-88","SC88","TSOT","T-SOT","T-SOC","TSOC","US-8","US8","SO-8","SO8","SO14","SO16","SO18","SO20","SO24","SO28","SO32","SOM-,"SOM14","SOM16","16-SOL","16SOL","SOM18","SOM20","SOM24","SOM28","SOM32","SOM-","10SON","10-SON","VSON","V-SON","LFCSP","LPQ","LMP","14SO","14-SO","IND","D2-PAK","D2PAK","CHIP","CERAMIC","SM-8","SM8","SOJ","TO-PMOD","TOPMOD","TO263","TO-263","TO236","TO-236","VINCU","CGA","CERPACK","CER-PACK","QGP","LLP","LGA","LTCC","MCM","MICRO SMD","MICROSMD","MICRO SMT","MICROSMT","LCC","CFP","SIDEBRAZE","SIDE BRAZE","UMAX","U-MAX","WSON","W-SON","CSP","DSP","COF","COB","SMA","SMC"
In Cell Y5
"T/H","TH ","DIFF","RECEPT","BASE-T","BASET","RCPT","POS","HDR","HEADER","PIN","SW","SWITCH","PIP","AXIAL","RAD","RELAY","2-AG","2AG","MICROAB","MICRO-AB","MICRO AB","SIP","DIP","RECPT","TO-247","TO247","TO92","TO-92","TO-220","TO220","TO-3","TO3","TO-5","TO5","TO-18","TO18","TO-39","TO39","TO-46","TO46","TO-66","TO66","TO-99","TO99","TO-100","TO100","TO-226","TO226","TO-251","TO251","TO-252","TO252","TO-262","TO262","TO-263","TO263","TO-274","TO274","TACT","REED","JUMPER","JMP","JP","VARIS","VARA","USB","MOD","ELECTROLYTIC","CONN","RESON","IC","SOCK","QIP","QIL","ZIP","OPGA","FCPGA","PAC","PGA","HOLDER","HEADER","CARBON FILM","CARBFILM","CARB FILM","METAL OXIDE","METAL FILM","POTENTIOMETER","POT ","TRIMMER","METALLIZED","MET ","MET-","MEA ","MEA-","CAP FILM MKT","CAP FILM","METAL POLY"
In Cell Z5
"SCREW","SCR","SC ","NUT","WASHER","WS","INSULATION","INSUL","HEAT SHRINK","HEATSHRINK","HT SHRINK","HTSHRINK","HEATSINK","HEAT SINK","GLUE","APOXY","EPOXY","BRACKET","BRACK","WIRE","WR","CABLE","CBL","TERMINAL","TERM","CRIMP","CMP","JACK","JCK","JK","BATT","TUBE","LOCKBAR","LOCK BAR","CARBON","ZINC","ENCLOS","STAINLESS STEEL","BRACE","FRAME","BLOCK","SPRING","PLATE","STRAIN REL","LID","BUMPER","BUZZER","CLIP","SPACER","LABEL","STRAP","BOOT","COVER","HEX","PHILIPS","PHILLIPS","TUBE","ADHESIVE","ADHES","TUBING","FASTENER","FASTNER","PLUNGER","PLUNG","CHASSIS","RUBBER","ALUM","RAIL","FUSEHLDR","KEYBOARD","THERM PAD","THERMAL PAD","DISPLAY","DISP","KNOB","LATCH","HOUSING","HOUSEING","AWG","PLUG","PUTTY","ENDCAP","END CAP","STANDOFF","STAND-OFF","STAND OFF","GROMET","GROMMET","CABLE TIE","CBL TIE","CB TIE","SPLICE","GASK","BUTTON","BUT","CAP","ORING","O-RING","TOUCHSCREEN","TOUCH SCREEN","0-80","1-64","1-72","2-56","2-64","3-48","3-56","4-40","4-48","5-40","5-44","6-32","6-40","8-32","8-36","10-24","10-32","12-24","12-28","CONN RING","CONN QC","SPEAKER","SPKR"
Now I get error in SEARCH formulas --> ex. SEARCH({$X$5},$F5)
Formula alone without MACRO works great. Please help.
