How delete words in text by macro

[i-m-t]

Hi everyone. I prepared a huge list of formulas in two rows (A2 and A5). On the left row there are product codes and on the right row there are product descriptions. In the description text, the product code and product specifications are written. What I would like to do is to prepare a macro so that it will check A2 and A5 and when it finds the same code, it should delete the code from the text on A5 so that the code wil only be available on A2. This should continue to A120. Would you please help me?

 

[stanleydgromjr]

i-m-t,

Post a screenshot of your worksheet with Colo's HTML Maker.

[url]http://www.puremis.net/excel/downloads.shtml
[/url]

Have a great day,
Stan

 

[QuietRiot]

im really confused by what you want

you have 2 rows that you want remove text from one of the rows but then you say continue to A120

can you post sample of the sheet using one of the html tools

or can you explain it better.

 

[i-m-t]

I am sorry you are right. I have two columns as B and D. The list is going upto B120 and D120. Sorry.

 

[QuietRiot]

I am sorry you are right. I have two columns as B and D. The list is going upto B120 and D120. Sorry.

ok,

and you want it to remove the product code from D which is basically found in column B

post a sample.

this isnt hard at all. if its what im thinking just a simple grab the value from B then do a replace in D

 

[i-m-t]

I wish I could but I do not know how to compare two colums and delete a part of text using a macro I can only make very simple things like copy - paste etc by macro.

 

[QuietRiot]

we will help you.

but we need some sample data.

i got an idea on how to code it but without sample data I can't get too far.

 

[i-m-t]

For example on B column there are these values:

SMQR 501 L
SMQR 300 A
SMSC 500 SC

And on column D there are these values:

SMQR 501 L red leds, satin stainless steel...
SMQR 300 A blue leds, mirror stainless steel...
SMSC 500 SC red leds, mirror stainless steel...

This goes upto row 125 that way. I would like to take out the text from column Dwhich is the same on column B.

 

[QuietRiot]

Sub hey()

Dim bcol As String, dcolnew As String
Last = Range("B65536").End(xlUp).Row

For x = 1 To Last
    bcol = Cells(x, 2).Value
    dcolnew = Cells(x, 4).Value
    dcolnew = Replace(dcolnew, bcol, "", 1)
    Cells(x, 4).Formula = dcolnew
Next
End Sub