Hello
I'm really struggling on this... I have a column with 000s of entries and I'd like to use vba to search the column for some text, and if found replace a different cell in a different column but the same row. The searched for text is a fixed list of approx. dozen items, and it will always be found at the beginning of the column, and only once in the entire column. So if Sheet2-A9 is found in cell Sheet1-B8950 then Sheet2-B9 replaces the contents of Sheet1-A8950. Sheet1-Column A has existing formulas so the macro will be overwriting its contents.
I'm completely new to VBA but have tried to adapt:
Excel: Find and Replace VBA, target cell, parent cells
http://www.mrexcel.com/forum/excel-questions/600081-macro-find-replace-offset.html
but can't get them working.
Please see my example:
sheet1:
Sheet1[TABLE="class: html-maker-worksheet"]
<thead>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[/TR]
</thead><tbody>[TR]
[TH]1[/TH]
[TD]4PCE[/TD]
[TD]4PCE MOND/G/SET-RS FLORAL LARGE & SMALL POT # 114.75 [/TD]
[/TR]
[TR]
[TH]2[/TH]
[TD]4PCE[/TD]
[TD]4PCE MOND/G/SET-RR FLORAL LARGE & SMALL POT # 240.75 [/TD]
[/TR]
[TR]
[TH]3[/TH]
[TD]test1[/TD]
[TD]test1 test[/TD]
[/TR]
[TR]
[TH]4[/TH]
[TD]test2[/TD]
[TD]test2 test[/TD]
[/TR]
[TR]
[TH]5[/TH]
[TD]5PCE[/TD]
[TD]5PCE POND/G/SET-RS FLORAL LARGE & MED POT # 118.75 [/TD]
[/TR]
[TR]
[TH]6[/TH]
[TD]5PCE[/TD]
[TD]5PCE POND/G/SET-RR FLORAL LARGE & MED POT # 248.95 [/TD]
[/TR]
[TR]
[TH]7[/TH]
[TD]COMBS01/SET-RS[/TD]
[TD]COMBS01/SET-RS PLAIN MEN'S COMBS & TIE # 59.25 [/TD]
[/TR]
[TR]
[TH]8[/TH]
[TD]COMBS02/SET-RS[/TD]
[TD]COMBS02/SET-RS DETAIL MEN'S COMBS & TIE # 59.25 [/TD]
[/TR]
[TR]
[TH]9[/TH]
[TD]FB2-RR-BLA[/TD]
[TD]FB2-RR-BLA CANDLE STICK - KEY # 33.25 [/TD]
[/TR]
[TR]
[TH]10[/TH]
[TD]test3[/TD]
[TD]test3 test[/TD]
[/TR]
[TR]
[TH]11[/TH]
[TD]FB5*PIP-RR-CRE[/TD]
[TD]FB5*PIP-RR-CRE CANDLE STICK - CREAM - KEY - PIP BOX # 41.5 [/TD]
[/TR]
[TR]
[TH]12[/TH]
[TD]test4[/TD]
[TD]test4 test[/TD]
[/TR]
[TR]
[TH]13[/TH]
[TD]test5[/TD]
[TD]test5 test[/TD]
[/TR]
[TR]
[TH]14[/TH]
[TD]test6[/TD]
[TD]test6 test[/TD]
[/TR]
[TR]
[TH]15[/TH]
[TD]MS1-RR-BLUE[/TD]
[TD]MS1-RR-BLUE NEW MODEL-BLUE-KEY # 16.25 [/TD]
[/TR]
[TR]
[TH]16[/TH]
[TD]test7[/TD]
[TD]test7 test[/TD]
[/TR]
[TR]
[TH]17[/TH]
[TD]test8[/TD]
[TD]test8 test[/TD]
[/TR]
[TR]
[TH]18[/TH]
[TD]MS1/U-RR-BLUE[/TD]
[TD]MS1/U-RR-BLUE NEW MODEL-BLUE-KEY # 16.25 [/TD]
[/TR]
[TR]
[TH]19[/TH]
[TD]test9[/TD]
[TD]test9 test[/TD]
[/TR]
[TR]
[TH]20[/TH]
[TD]SMALL[/TD]
[TD]SMALL MOND/G-RS FLORAL SMALL POT # 58.5 [/TD]
[/TR]
[TR]
[TH]21[/TH]
[TD]SMALL[/TD]
[TD]SMALL POND/G-RS FLORAL SMALL POT # 72.3 [/TD]
[/TR]
</tbody>[/TABLE]
Excel 2003
<tbody>
[TD="bgcolor: #FFFFFF"] Worksheet Formulas [TABLE="class: html-maker-worksheet"]
<thead>[TR]
</thead><tbody>
</tbody>[/TD]
[/TR]
</tbody>[/TABLE]
sheet 2:
Sheet2[TABLE="class: html-maker-worksheet"]
<thead>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[/TR]
</thead><tbody>[TR]
[TH]1[/TH]
[TD]4PCE MOND/G/SET-RS[/TD]
[TD]4PCE MOND/G/SET-RS[/TD]
[/TR]
[TR]
[TH]2[/TH]
[TD]4PCE MOND/G/SET-RR[/TD]
[TD]4PCE MOND/G/SET-RR[/TD]
[/TR]
[TR]
[TH]3[/TH]
[TD]5PCE POND/G/SET-RS[/TD]
[TD]5PCCPONDGSET-RS[/TD]
[/TR]
[TR]
[TH]4[/TH]
[TD]5PCE POND/G/SET-RR[/TD]
[TD]5PCEPONDGSET-RR[/TD]
[/TR]
[TR]
[TH]5[/TH]
[TD]COMBS01/SET-RS[/TD]
[TD]COMBS01/SET-RS[/TD]
[/TR]
[TR]
[TH]6[/TH]
[TD]COMBS02/SET-RS[/TD]
[TD]COMBS02SET-RS[/TD]
[/TR]
[TR]
[TH]7[/TH]
[TD]FB2-RR-BLA[/TD]
[TD]FB2-RR-BLA[/TD]
[/TR]
[TR]
[TH]8[/TH]
[TD]FB5*PIP-RR-CRE[/TD]
[TD]FB5*PIP-RR-CRE[/TD]
[/TR]
[TR]
[TH]9[/TH]
[TD]FB5-RR-BLA[/TD]
[TD]FB5-RR-BLA[/TD]
[/TR]
[TR]
[TH]10[/TH]
[TD]MS1-RR-BLUE[/TD]
[TD]MS1-RR-BLUE[/TD]
[/TR]
[TR]
[TH]11[/TH]
[TD]MS1/U-RR-BLUE[/TD]
[TD]MS1/U-RR-BLUE[/TD]
[/TR]
[TR]
[TH]12[/TH]
[TD]SMALL MOND/G-RS[/TD]
[TD]SMALL MOND/G-RS[/TD]
[/TR]
[TR]
[TH]13[/TH]
[TD]SMALL POND/G-RS[/TD]
[TD]SMALPONDG-RS[/TD]
[/TR]
</tbody>[/TABLE]
Excel 2003
Hugely grateful for any enlightenment on this!!!
I'm really struggling on this... I have a column with 000s of entries and I'd like to use vba to search the column for some text, and if found replace a different cell in a different column but the same row. The searched for text is a fixed list of approx. dozen items, and it will always be found at the beginning of the column, and only once in the entire column. So if Sheet2-A9 is found in cell Sheet1-B8950 then Sheet2-B9 replaces the contents of Sheet1-A8950. Sheet1-Column A has existing formulas so the macro will be overwriting its contents.
I'm completely new to VBA but have tried to adapt:
Excel: Find and Replace VBA, target cell, parent cells
http://www.mrexcel.com/forum/excel-questions/600081-macro-find-replace-offset.html
but can't get them working.
Please see my example:
sheet1:
Sheet1[TABLE="class: html-maker-worksheet"]
<thead>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[/TR]
</thead><tbody>[TR]
[TH]1[/TH]
[TD]4PCE[/TD]
[TD]4PCE MOND/G/SET-RS FLORAL LARGE & SMALL POT # 114.75 [/TD]
[/TR]
[TR]
[TH]2[/TH]
[TD]4PCE[/TD]
[TD]4PCE MOND/G/SET-RR FLORAL LARGE & SMALL POT # 240.75 [/TD]
[/TR]
[TR]
[TH]3[/TH]
[TD]test1[/TD]
[TD]test1 test[/TD]
[/TR]
[TR]
[TH]4[/TH]
[TD]test2[/TD]
[TD]test2 test[/TD]
[/TR]
[TR]
[TH]5[/TH]
[TD]5PCE[/TD]
[TD]5PCE POND/G/SET-RS FLORAL LARGE & MED POT # 118.75 [/TD]
[/TR]
[TR]
[TH]6[/TH]
[TD]5PCE[/TD]
[TD]5PCE POND/G/SET-RR FLORAL LARGE & MED POT # 248.95 [/TD]
[/TR]
[TR]
[TH]7[/TH]
[TD]COMBS01/SET-RS[/TD]
[TD]COMBS01/SET-RS PLAIN MEN'S COMBS & TIE # 59.25 [/TD]
[/TR]
[TR]
[TH]8[/TH]
[TD]COMBS02/SET-RS[/TD]
[TD]COMBS02/SET-RS DETAIL MEN'S COMBS & TIE # 59.25 [/TD]
[/TR]
[TR]
[TH]9[/TH]
[TD]FB2-RR-BLA[/TD]
[TD]FB2-RR-BLA CANDLE STICK - KEY # 33.25 [/TD]
[/TR]
[TR]
[TH]10[/TH]
[TD]test3[/TD]
[TD]test3 test[/TD]
[/TR]
[TR]
[TH]11[/TH]
[TD]FB5*PIP-RR-CRE[/TD]
[TD]FB5*PIP-RR-CRE CANDLE STICK - CREAM - KEY - PIP BOX # 41.5 [/TD]
[/TR]
[TR]
[TH]12[/TH]
[TD]test4[/TD]
[TD]test4 test[/TD]
[/TR]
[TR]
[TH]13[/TH]
[TD]test5[/TD]
[TD]test5 test[/TD]
[/TR]
[TR]
[TH]14[/TH]
[TD]test6[/TD]
[TD]test6 test[/TD]
[/TR]
[TR]
[TH]15[/TH]
[TD]MS1-RR-BLUE[/TD]
[TD]MS1-RR-BLUE NEW MODEL-BLUE-KEY # 16.25 [/TD]
[/TR]
[TR]
[TH]16[/TH]
[TD]test7[/TD]
[TD]test7 test[/TD]
[/TR]
[TR]
[TH]17[/TH]
[TD]test8[/TD]
[TD]test8 test[/TD]
[/TR]
[TR]
[TH]18[/TH]
[TD]MS1/U-RR-BLUE[/TD]
[TD]MS1/U-RR-BLUE NEW MODEL-BLUE-KEY # 16.25 [/TD]
[/TR]
[TR]
[TH]19[/TH]
[TD]test9[/TD]
[TD]test9 test[/TD]
[/TR]
[TR]
[TH]20[/TH]
[TD]SMALL[/TD]
[TD]SMALL MOND/G-RS FLORAL SMALL POT # 58.5 [/TD]
[/TR]
[TR]
[TH]21[/TH]
[TD]SMALL[/TD]
[TD]SMALL POND/G-RS FLORAL SMALL POT # 72.3 [/TD]
[/TR]
</tbody>[/TABLE]
Excel 2003
Cell | Formula |
---|---|
A1 | =LEFT(B1,FIND(" ",B1)-1) |
A2 | =LEFT(B2,FIND(" ",B2)-1) |
A3 | =LEFT(B3,FIND(" ",B3)-1) |
A4 | =LEFT(B4,FIND(" ",B4)-1) |
A5 | =LEFT(B5,FIND(" ",B5)-1) |
A6 | =LEFT(B6,FIND(" ",B6)-1) |
A7 | =LEFT(B7,FIND(" ",B7)-1) |
A8 | =LEFT(B8,FIND(" ",B8)-1) |
A9 | =LEFT(B9,FIND(" ",B9)-1) |
A10 | =LEFT(B10,FIND(" ",B10)-1) |
A11 | =LEFT(B11,FIND(" ",B11)-1) |
A12 | =LEFT(B12,FIND(" ",B12)-1) |
A13 | =LEFT(B13,FIND(" ",B13)-1) |
A14 | =LEFT(B14,FIND(" ",B14)-1) |
A15 | =LEFT(B15,FIND(" ",B15)-1) |
A16 | =LEFT(B16,FIND(" ",B16)-1) |
A17 | =LEFT(B17,FIND(" ",B17)-1) |
A18 | =LEFT(B18,FIND(" ",B18)-1) |
A19 | =LEFT(B19,FIND(" ",B19)-1) |
A20 | =LEFT(B20,FIND(" ",B20)-1) |
A21 | =LEFT(B21,FIND(" ",B21)-1) |
<tbody>
[TD="bgcolor: #FFFFFF"] Worksheet Formulas [TABLE="class: html-maker-worksheet"]
<thead>[TR]
</thead><tbody>
</tbody>
[/TR]
</tbody>[/TABLE]
sheet 2:
Sheet2[TABLE="class: html-maker-worksheet"]
<thead>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[/TR]
</thead><tbody>[TR]
[TH]1[/TH]
[TD]4PCE MOND/G/SET-RS[/TD]
[TD]4PCE MOND/G/SET-RS[/TD]
[/TR]
[TR]
[TH]2[/TH]
[TD]4PCE MOND/G/SET-RR[/TD]
[TD]4PCE MOND/G/SET-RR[/TD]
[/TR]
[TR]
[TH]3[/TH]
[TD]5PCE POND/G/SET-RS[/TD]
[TD]5PCCPONDGSET-RS[/TD]
[/TR]
[TR]
[TH]4[/TH]
[TD]5PCE POND/G/SET-RR[/TD]
[TD]5PCEPONDGSET-RR[/TD]
[/TR]
[TR]
[TH]5[/TH]
[TD]COMBS01/SET-RS[/TD]
[TD]COMBS01/SET-RS[/TD]
[/TR]
[TR]
[TH]6[/TH]
[TD]COMBS02/SET-RS[/TD]
[TD]COMBS02SET-RS[/TD]
[/TR]
[TR]
[TH]7[/TH]
[TD]FB2-RR-BLA[/TD]
[TD]FB2-RR-BLA[/TD]
[/TR]
[TR]
[TH]8[/TH]
[TD]FB5*PIP-RR-CRE[/TD]
[TD]FB5*PIP-RR-CRE[/TD]
[/TR]
[TR]
[TH]9[/TH]
[TD]FB5-RR-BLA[/TD]
[TD]FB5-RR-BLA[/TD]
[/TR]
[TR]
[TH]10[/TH]
[TD]MS1-RR-BLUE[/TD]
[TD]MS1-RR-BLUE[/TD]
[/TR]
[TR]
[TH]11[/TH]
[TD]MS1/U-RR-BLUE[/TD]
[TD]MS1/U-RR-BLUE[/TD]
[/TR]
[TR]
[TH]12[/TH]
[TD]SMALL MOND/G-RS[/TD]
[TD]SMALL MOND/G-RS[/TD]
[/TR]
[TR]
[TH]13[/TH]
[TD]SMALL POND/G-RS[/TD]
[TD]SMALPONDG-RS[/TD]
[/TR]
</tbody>[/TABLE]
Excel 2003
Hugely grateful for any enlightenment on this!!!