VBA Find and Replace from Cell Contents

jwalkins

New Member
Joined
Jun 29, 2015
Messages
3
Hi,

I'm quite new to VBA and I'm struggling to find a solution to this. I'm trying to delete the value from Column 2 and Column 3 if it contains the value in its corresponding row in Column 1. i.e. for the first row delete 94000 from Column 3, then loop to the next column and delete 50000 from the Column 2. The searched for text needs to be the value in Column 1 and delete the value in Column 2 / 3 if it matches that of Column 1.

Any help would be much appreciated!

Column 1
Column 2
Column 3
94000
SAN RAMONCA 94000
50000
CA, 50000
0400
NH03780-0400
60000
Batavia, IL60000
90000
CA90000

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,506
Why don't you have calculation columns 4 and 5 to get the cleaned versions of columns 2 and 3?

Like:
=SUBSTITUTE(B2,$A2,"")
 

jwalkins

New Member
Joined
Jun 29, 2015
Messages
3
Thanks for the quick response GlennUK. I hadn't actually thought about using SUBSTITUTE so I gave it a go, the issue that appears now is beacuse the table is a "paste values" from another sheet with formulas so excel has stored the number as text in column 1, meaning the SUBSTITUTE formula isn't working as it should. Is there a work around to make the numbers past as numbers (and keep the 0 at the front of the number?)

Thanks
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,506
Try formatting the target area as Text prior to the copy/paste operation.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,193
Messages
5,594,767
Members
413,933
Latest member
Msar5586

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top