Find and Replace Multiple Values in Excel using VBA Macro
This tutorial explains how to find and replace multiple values in excel using VBA. VBA Code to find and replace values in column or range is very simple and helpful in completing the task quickly.
Пікірлер: 103
You are a lifesaver! Please never stop making basic VBA videos like this! You are helping a lot of idiots like me to save time! I was going to nest a lot of "IF ISNUMBER SEARCH" but it reached the limited lol This is a much elegant way! Thank you!
@ExcelDestination
4 жыл бұрын
Glad to help!
@priyankamaduraiveeran524
4 жыл бұрын
same i have also used if isnumber search
This video was outstanding... thank you so much! This was a life-saver for me this morning... really appreciate it!
Text in Portuguese, translated by google ... Dear friend, I don't know what part of the world you are from, but it helped me a lot with this tip! Thank you so much!
@ExcelDestination
3 жыл бұрын
Glad I could help!
Oh My, Works became easier, Thanks for your tutorial, Love you.
@ExcelDestination
4 жыл бұрын
thank you!
I was looking for this method for some time. Thank you for sharing
@ExcelDestination
Жыл бұрын
My pleasure 😊 🙂
Thank you very much, great video and special explanation.
thank you so much for creating this formula! It has helped me immensely and saved so much time!
@ExcelDestination
3 жыл бұрын
You're so welcome!
Very helpful video and explained clearly 👍🏼 In case we want to find the text “Mumbai” in one column say A and if present, replace the value in the column next to it i.e B with “MUM”
Thanks a lot. Very helpful video.
Great tutorial. Thank you sir!
@ExcelDestination
3 жыл бұрын
Glad it was helpful!
thanks very inspiring..pls share more macro videos.
Thank you so much sir, i used in this way before I had using find and replace command around 24 replacement but now easily to replace
@ExcelDestination
3 жыл бұрын
Great 👍
Thanks Mate. It worked perfectly.
@ExcelDestination
2 жыл бұрын
You're welcome!
Thanks for sharing I used to speed up a process at work. !
@ExcelDestination
3 жыл бұрын
You're welcome!
Thanks! Good video
wow......❤
Thank you for your videos, they are very helpful! quick question, what does lookat:=xlpart do?
Excelent! Thanks!
@ExcelDestination
3 жыл бұрын
You're welcome!
I have to replace 1254 items, I copy and paste the code 1254 times one and I gets slow but it worked if the scrip is divided on 2 different macros, is there a way to optimize this code for this case, when the list of items is raelly long like more than 2k items?
That's a very useful tutorial, thank you! I also wanted to have it a bit more complex as if the source and target values are located at another worksheet, how can we use them?
@ExcelDestination
4 жыл бұрын
you can take help from some other tutorials, I have uploaded for copy and paste.........and copy paste on basis of conditions.
many thanks for the amazing code but i have question if i have multiple sheets and want to do this process in all multiple sheets many thanks in advance
How we can use this ..if we have replacement data in same file another sheet ?? The steps need .. Do i need to enter steps before for loop or inbetween
Hi, can you please paste your VBA code into comments section? Thanks
@binhubonetti
3 жыл бұрын
Sub MacroSubstituir() For i = 1 To 50 Worksheets("Sheet1").Range("A1:A35").Select Selection.Replace What:=Cells(i, 3).Value, Replacement:=Cells(i, 4).Value, _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Next Worksheets("Sheet1").Cells(1, 1).Select End Sub
Sub Macro1() For i = 1 To 50 Worksheets("Sheet1").Range("A1:A35").Select Selection.Replace What:=Cells(i, 3).Value, Replacement:=Cells(i, 4).Value, _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Next Worksheets("Sheet1").Cells(1, 1).Select End Sub
Excellent Video, thanks for this :-)
really? You made your viewer enter the VBA code word by word manually?
@ExcelDestination
4 жыл бұрын
we are working on posting code at our website www.exceldestination.com
nice Video ,please suggest how to replace the data in Visio as per excel data
thank you for this is a live saver, but what about changing the whole string in the cell? is there a way to include this in code? no part of the text but the whole one, like a regular find and replace, EX: Find this: "This is the World" and Replace it by this : "Hellow Universe", in your case it will just include "Hellow universe" into "This is the world" atleast is what mine is doing or maybe im doing something wrong but it just subtitute the whole phrase if the one to implement it has more characters then the one to be replaced.
@ExcelDestination
4 жыл бұрын
I would say, instead of going by this example, you can use For Loop and If condition to check cell value completely and replace. there is one tutorial for this, you can take help from : kzread.info/dash/bejne/gKpr3I-ioNXVo7Q.html
Hi, can you make a tutorial for Add, Edit and Delete for VBA Excel?
I try to compare the text in excel and visio, if the text match, then replace that visio text with a range of excel text. Do anybody have any experiment with that
Will You be teaching Excel VBA? If yes then please let me know. Thanks
sir would you share macro regarding select all columns in one page according to A4 size across all EXCEL SHEETS
Thank you for the video! I have a couple questions as I try to fit this to my needs. Here are the edits I'm trying to make: 1) I would like the range where the find/replace is taking place to be an entire column such as "A:A", 2) I would like to store my What and Replacement ranges on a different sheet, and 3) I would like my What and Replacement ranges to come from the entire column as well, not a limited range like A2:A6. This is because I may need to add/edit these ranges overtime and don't want to adjust the code each time. Below is an example of the code I'm trying to use. It's not working yet, so any help is appreciated! Sub FindAndReplace() Worksheets("Output").Range("AZ:AZ").Select Selection.Replace What:=Worksheets("CodeTranslation").Range("A:A").Value, Replacement:=Worksheets("CodeTranslation").Range("B:B").Value, _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False End Sub
@AnmolKumar-jl3og
3 жыл бұрын
This would work in your case........ Sub FindAndReplace() Dim lastrow As Long, i As Long ThisWorkbook.Worksheets("Output").Range("AZ:AZ").Select lastrow = ThisWorkbook.Worksheets("CodeTranslation").Range("A1").End(xlDown).Row For i = 1 To lastrow Selection.Replace What:=ThisWorkbook.Worksheets("CodeTranslation").Range("A" & i).Value, _ Replacement:=ThisWorkbook.Worksheets("CodeTranslation").Range("B" & i).Value, _ LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False Next i End Sub
I want to highlight the replaced words only in next column or change font colour in same column... Pls help.. to identify what are the words replaced for future reference... Thanks.. how to do that tell
@ExcelDestination
4 жыл бұрын
you would require additional code for that
Thank you very much for this simple and useful tutorial. I wanna ask one more thing that how to create the button, I create one but when I click on it, nothing happens
This is Awesome! Is it possible to use this method to replace multiple text values in a single cell? The scenario is: paste Html code (entire email) into cell, and then have a panel of different things to find and replace, just like you have. And find and replace all values with one click? Will this script work or do i need to modify Something?
@ExcelDestination
4 жыл бұрын
You need to modify little bit.
@alerek
4 жыл бұрын
@@ExcelDestination ok, just change the range or is it more complicated?
Good video
@ExcelDestination
4 жыл бұрын
Thanks
Gud but I want to know if I don't no range of cell what will be the script
Instead of i = 1 to 32, how can you select the entire sheet to replace values on? And how would you reference the replacement columns/cells (i, 3 and i, 4) on another sheet?
@ExcelDestination
4 жыл бұрын
Thanks for your question. You can use Cells.Select to select the entire sheet.
can we create a function out of this one? that goes and get the data from a diffrent spreadsheet in the same woorkbook?
@ExcelDestination
4 жыл бұрын
Not sure about the function. But, I can provide you VBA Code for your task.
@elnegrocangry
4 жыл бұрын
Excel Destination I would I appreciate it. Thanks
@ExcelDestination
4 жыл бұрын
Send me email with dummy data at exceldestination@gmail.com
@GiveMeRomo
4 жыл бұрын
@@ExcelDestination Do you have a tutorial for this online? I need to do the same thing
@ExcelDestination
4 жыл бұрын
Thanks for watching! KZread tutorial is itself free online tutorial. If you need any excel vba support, you can write to exceldestination@gmail.com.
Hi, is it possible to do this loop using word documents?
@ExcelDestination
4 жыл бұрын
Yes, why not. We can automate tasks in MS Word also using VBA.
How do I add the button with the macro to the file ?
@ExcelDestination
4 жыл бұрын
you can check following tutorial : kzread.info/dash/bejne/h2igyI-AfpOxg9I.html
It's not working. I am facing problem.After click on button it shows error
Need it work with gaint key
please help me ... if i want to colour the replace word only in cell.. if it is possible then please help me....🙏🙏🙏🙏🏼🙏
@ExcelDestination
3 жыл бұрын
yes it is possible
@parmesht00
3 жыл бұрын
@@ExcelDestination could you explain what changes should done in this VBA????
@parmesht00
3 жыл бұрын
@@ExcelDestination hiii... please help asap...
Is there a way to do this so that it replaces whole words only?
@ExcelDestination
4 жыл бұрын
yes we can do that
@askthegardener
4 жыл бұрын
@@ExcelDestination Can you recommend any specific websites/tutorials, et cetera to help me learn how to do that?
What if the value to be replaced is in another column? For example, I still want the range of value to be found in column "A" but then replace the value in "column B" of the same row. How would the code change? (I sent you an email with the file and question). Thank you very much.
@viveksview7901
4 жыл бұрын
did you get how to replacethem in another column?
Hi, first Thanks for this video!!! Next my question. How can i find text and replace the ALL text with a number? Example: Colombia replaced by number "57" I have a sheet with country names and dialed digits that i need to separate only the country code to replace the country name. My issue is that some countries have for example Colombia Mobile so when i tried this it replaced Colombia with 57 - Mobile. I need to have just the 57. Please Help if you can would really appreciate it!! please please.
@ExcelDestination
3 жыл бұрын
for any customized code or excel vba project, you can write to exceldestination@gmail.com
Have Data Range(A2:A76467) And Replce Data i=4to101 but i not replace all data only a2 i row is replace
Hi guys I'm trying to use this however I get an error on a line of code
@juand221
3 жыл бұрын
I'm trying to use this however I get an error on a line of code
@ExcelDestination
3 жыл бұрын
without looking at your code, I can't say anything. If you want, you can share your code at exceldestination@gmail.com. I will try to look at that.
@juand221
3 жыл бұрын
Thank you so much!@@ExcelDestination I just sent you an e-mail
I keep getting object error 424 please help
@ExcelDestination
4 жыл бұрын
you can share your code at exceldestination@gmail.com
It keeps saying syntax error :(
@ExcelDestination
4 жыл бұрын
without looking at code, can not say anything. If you want, you can share your code at exceldestination@gmail.com
Request you to share excel to particle
@ExcelDestination
4 жыл бұрын
Will upload soon
It does not work.. it only works when find and replace keep in C3 and D3 onwards..
Runtime error '9': Subscription out of range
Doesn't work
@ExcelDestination
4 жыл бұрын
what is the error ?
@Pbuilder
4 жыл бұрын
@@ExcelDestination nothing happens after pressing command button
@ExcelDestination
4 жыл бұрын
send me your file at exceldestination@gmail.com
Hi every one he give me this message "Runtime error 1004 Application-defined or object defined error" her is my code "Private Sub CommandButton1_Click() For i = 2 To 62 Worksheets("Sheet1").Range("H2:H26722").Select Selection.Replace Waht:=Cells(i, 16).Value, Replacement:=Cells(i, 17).Value, _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=True Next Worksheets("Sheet1").Cells(1, 1).Select End Sub"