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

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,982
Office Version
  1. 2016
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
36,982
Office Version
  1. 2016
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,444
Messages
5,642,190
Members
417,259
Latest member
gtacw

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