Find and Replace VBA code

adrianux

New Member
Joined
May 29, 2015
Messages
15
Hello experts. I am learning VBA and I will like to make a function in VBA that will search in the column A a number from the column C and when it will be found, replace it with the number from the column B and then step to the next number in the column C until we get to the end of the column C.
Thanks you.

Here you have the example:
http://i59.tinypic.com/2daiqmo.jpg
 
Last edited:

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,893
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Does this macro do what you want...
Code:
Sub FindAndReplace()
  Dim N As Double
  For N = 1 To Cells(Rows.Count, "C").End(xlUp).Row
    Columns("A").Replace Cells(N, "C").Value, Cells(N, "B").Value, xlWhole
  Next
End Sub
 

adrianux

New Member
Joined
May 29, 2015
Messages
15
Actually this little script does not works verry well. If I have in the column A the text " AAAASSSDD1234" and in the column B the text "12345" and the search text in the column C is "1234" it replaces all the column A with the new value from the column B, so it will delete the text "AAAASSSDD".
I need to replace with the new value but keeping the rest of the text in the column A.
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,893
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

Actually this little script does not works verry well. If I have in the column A the text " AAAASSSDD1234" and in the column B the text "12345" and the search text in the column C is "1234" it replaces all the column A with the new value from the column B, so it will delete the text "AAAASSSDD".
I need to replace with the new value but keeping the rest of the text in the column A.
Does this do what you want (I made a minor tweak to my previous code)...
Code:
Sub FindAndReplace()
  Dim N As Double
  For N = 1 To Cells(Rows.Count, "C").End(xlUp).Row
    Columns("A").Replace Cells(N, "C").Value, Cells(N, "B").Value, xlPart
  Next
End Sub
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,517
Messages
5,837,794
Members
430,516
Latest member
thaling

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