Creating a macro to find and replace text
Hi all
Can anybody help? I have created very basic macros in Excel but have no knowledge whatsoever of Visual Basic. Is it possible to create a macro that will search for a particular word(s) in the whole of an Excel workbook and replace it with another? Would I simply go into Record mode and select Edit, Find & Replace and enter the text? How would this be applied to the whole workbook rather than the active sheet? Any help would be appreciated. Thank you Louise |
Hello Louise!
I have just make something similar last week, maybe you can use this: sub replace () What = InputBox("word to search") repl = InputBox("word to replace") Sheets().Select Selection.Replace What:=What, Replacement:=repl, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End Sub -- sisco98 "Louise" wrote: Hi all Can anybody help? I have created very basic macros in Excel but have no knowledge whatsoever of Visual Basic. Is it possible to create a macro that will search for a particular word(s) in the whole of an Excel workbook and replace it with another? Would I simply go into Record mode and select Edit, Find & Replace and enter the text? How would this be applied to the whole workbook rather than the active sheet? Any help would be appreciated. Thank you Louise |
Yes, you may record a macro to do what you want. It probably ends up looking
something like this. You will find what you have recorded by opening Visual Basic (Tools, Macro, Visiual Basic Editor) and then look for the recorded macro in Modules and in Module1 etc You can also copy my code into one a module if you wish. If you do make sure that the sheet names in the code correspond to what is in your workbook. Sub FindData1ReplaceData2() Sheets("Sheet1").Select Cells.Replace What:="Data1", Replacement:="Data2", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Sheets("Sheet2").Select Cells.Replace What:="Data1", Replacement:="Data2", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Sheets("Sheet3").Select Cells.Replace What:="Data1", Replacement:="Data2", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False End Sub -- Regards, Martin "Louise" wrote: Hi all Can anybody help? I have created very basic macros in Excel but have no knowledge whatsoever of Visual Basic. Is it possible to create a macro that will search for a particular word(s) in the whole of an Excel workbook and replace it with another? Would I simply go into Record mode and select Edit, Find & Replace and enter the text? How would this be applied to the whole workbook rather than the active sheet? Any help would be appreciated. Thank you Louise |
Thank you for your prompt replies.
I'll give this a go. THanks again. Louise "sisco98" wrote: Hello Louise! I have just make something similar last week, maybe you can use this: sub replace () What = InputBox("word to search") repl = InputBox("word to replace") Sheets().Select Selection.Replace What:=What, Replacement:=repl, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End Sub -- sisco98 "Louise" wrote: Hi all Can anybody help? I have created very basic macros in Excel but have no knowledge whatsoever of Visual Basic. Is it possible to create a macro that will search for a particular word(s) in the whole of an Excel workbook and replace it with another? Would I simply go into Record mode and select Edit, Find & Replace and enter the text? How would this be applied to the whole workbook rather than the active sheet? Any help would be appreciated. Thank you Louise |
i've checked and for second time isn't work.:-(
you could try this one too: Sub replacerev() What = InputBox("word to search") repl = InputBox("word to replace") Cells.replace What:=What, Replacement:=repl, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End Sub -- sisco98 "Louise" wrote: Thank you for your prompt replies. I'll give this a go. THanks again. Louise "sisco98" wrote: Hello Louise! I have just make something similar last week, maybe you can use this: sub replace () What = InputBox("word to search") repl = InputBox("word to replace") Sheets().Select Selection.Replace What:=What, Replacement:=repl, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End Sub -- sisco98 "Louise" wrote: Hi all Can anybody help? I have created very basic macros in Excel but have no knowledge whatsoever of Visual Basic. Is it possible to create a macro that will search for a particular word(s) in the whole of an Excel workbook and replace it with another? Would I simply go into Record mode and select Edit, Find & Replace and enter the text? How would this be applied to the whole workbook rather than the active sheet? Any help would be appreciated. Thank you Louise |
Thanks for this. I've tried this one and it works fine on individual
worksheets, however, is there a way you can ask it to run on the whole workbook?? Thanks. Louise "sisco98" wrote: i've checked and for second time isn't work.:-( you could try this one too: Sub replacerev() What = InputBox("word to search") repl = InputBox("word to replace") Cells.replace What:=What, Replacement:=repl, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End Sub -- sisco98 "Louise" wrote: Thank you for your prompt replies. I'll give this a go. THanks again. Louise "sisco98" wrote: Hello Louise! I have just make something similar last week, maybe you can use this: sub replace () What = InputBox("word to search") repl = InputBox("word to replace") Sheets().Select Selection.Replace What:=What, Replacement:=repl, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End Sub -- sisco98 "Louise" wrote: Hi all Can anybody help? I have created very basic macros in Excel but have no knowledge whatsoever of Visual Basic. Is it possible to create a macro that will search for a particular word(s) in the whole of an Excel workbook and replace it with another? Would I simply go into Record mode and select Edit, Find & Replace and enter the text? How would this be applied to the whole workbook rather than the active sheet? Any help would be appreciated. Thank you Louise |
Hi Louise,
Your welcome. Sorry for the late answer, this macro works on all worksheet: Sub replaceallsheet() Worksheets.Select What = InputBox("word to search") repl = InputBox("word to replace") Cells.Replace What:=What, Replacement:=repl, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End Sub Gábor -- sisco98 "Louise" wrote: Thanks for this. I've tried this one and it works fine on individual worksheets, however, is there a way you can ask it to run on the whole workbook?? Thanks. Louise "sisco98" wrote: i've checked and for second time isn't work.:-( you could try this one too: Sub replacerev() What = InputBox("word to search") repl = InputBox("word to replace") Cells.replace What:=What, Replacement:=repl, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End Sub -- sisco98 "Louise" wrote: Thank you for your prompt replies. I'll give this a go. THanks again. Louise "sisco98" wrote: Hello Louise! I have just make something similar last week, maybe you can use this: sub replace () What = InputBox("word to search") repl = InputBox("word to replace") Sheets().Select Selection.Replace What:=What, Replacement:=repl, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End Sub -- sisco98 "Louise" wrote: Hi all Can anybody help? I have created very basic macros in Excel but have no knowledge whatsoever of Visual Basic. Is it possible to create a macro that will search for a particular word(s) in the whole of an Excel workbook and replace it with another? Would I simply go into Record mode and select Edit, Find & Replace and enter the text? How would this be applied to the whole workbook rather than the active sheet? Any help would be appreciated. Thank you Louise |
Hi
Thanks again for your reply, however, I can still only get this to work on each individual sheet rather than the whole book?? Thanks. Louise "sisco98" wrote: Hi Louise, Your welcome. Sorry for the late answer, this macro works on all worksheet: Sub replaceallsheet() Worksheets.Select What = InputBox("word to search") repl = InputBox("word to replace") Cells.Replace What:=What, Replacement:=repl, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End Sub Gábor -- sisco98 "Louise" wrote: Thanks for this. I've tried this one and it works fine on individual worksheets, however, is there a way you can ask it to run on the whole workbook?? Thanks. Louise "sisco98" wrote: i've checked and for second time isn't work.:-( you could try this one too: Sub replacerev() What = InputBox("word to search") repl = InputBox("word to replace") Cells.replace What:=What, Replacement:=repl, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End Sub -- sisco98 "Louise" wrote: Thank you for your prompt replies. I'll give this a go. THanks again. Louise "sisco98" wrote: Hello Louise! I have just make something similar last week, maybe you can use this: sub replace () What = InputBox("word to search") repl = InputBox("word to replace") Sheets().Select Selection.Replace What:=What, Replacement:=repl, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End Sub -- sisco98 "Louise" wrote: Hi all Can anybody help? I have created very basic macros in Excel but have no knowledge whatsoever of Visual Basic. Is it possible to create a macro that will search for a particular word(s) in the whole of an Excel workbook and replace it with another? Would I simply go into Record mode and select Edit, Find & Replace and enter the text? How would this be applied to the whole workbook rather than the active sheet? Any help would be appreciated. Thank you Louise |
sorry, try this one. you should repeat the last part as many sheets you have,
and of course if the name of your sheets are different, you should change them as well. if still not work, please contact me, i'm sure we can find some solution Sub rpl() Sheets("sheet1").Select What = InputBox("word to search") repl = InputBox("word to replace") Cells.Replace What:=What, Replacement:=repl, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Sheets("sheet2").Select Cells.Replace What:=What, Replacement:=repl, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Sheets("sheet3").Select Cells.Replace What:=What, Replacement:=repl, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End Sub cheers-- sisco98 "Louise" wrote: Hi Thanks again for your reply, however, I can still only get this to work on each individual sheet rather than the whole book?? Thanks. Louise "sisco98" wrote: Hi Louise, Your welcome. Sorry for the late answer, this macro works on all worksheet: Sub replaceallsheet() Worksheets.Select What = InputBox("word to search") repl = InputBox("word to replace") Cells.Replace What:=What, Replacement:=repl, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End Sub Gábor -- sisco98 "Louise" wrote: Thanks for this. I've tried this one and it works fine on individual worksheets, however, is there a way you can ask it to run on the whole workbook?? Thanks. Louise "sisco98" wrote: i've checked and for second time isn't work.:-( you could try this one too: Sub replacerev() What = InputBox("word to search") repl = InputBox("word to replace") Cells.replace What:=What, Replacement:=repl, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End Sub -- sisco98 "Louise" wrote: Thank you for your prompt replies. I'll give this a go. THanks again. Louise "sisco98" wrote: Hello Louise! I have just make something similar last week, maybe you can use this: sub replace () What = InputBox("word to search") repl = InputBox("word to replace") Sheets().Select Selection.Replace What:=What, Replacement:=repl, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End Sub -- sisco98 "Louise" wrote: Hi all Can anybody help? I have created very basic macros in Excel but have no knowledge whatsoever of Visual Basic. Is it possible to create a macro that will search for a particular word(s) in the whole of an Excel workbook and replace it with another? Would I simply go into Record mode and select Edit, Find & Replace and enter the text? How would this be applied to the whole workbook rather than the active sheet? Any help would be appreciated. Thank you Louise |
Thank you, once again!!
This works fine. I didn't realise I would have to repeat it for each worksheet. Thanks again for all your help. Louise "sisco98" wrote: sorry, try this one. you should repeat the last part as many sheets you have, and of course if the name of your sheets are different, you should change them as well. if still not work, please contact me, i'm sure we can find some solution Sub rpl() Sheets("sheet1").Select What = InputBox("word to search") repl = InputBox("word to replace") Cells.Replace What:=What, Replacement:=repl, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Sheets("sheet2").Select Cells.Replace What:=What, Replacement:=repl, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Sheets("sheet3").Select Cells.Replace What:=What, Replacement:=repl, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End Sub cheers-- sisco98 "Louise" wrote: Hi Thanks again for your reply, however, I can still only get this to work on each individual sheet rather than the whole book?? Thanks. Louise "sisco98" wrote: Hi Louise, Your welcome. Sorry for the late answer, this macro works on all worksheet: Sub replaceallsheet() Worksheets.Select What = InputBox("word to search") repl = InputBox("word to replace") Cells.Replace What:=What, Replacement:=repl, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End Sub Gábor -- sisco98 "Louise" wrote: Thanks for this. I've tried this one and it works fine on individual worksheets, however, is there a way you can ask it to run on the whole workbook?? Thanks. Louise "sisco98" wrote: i've checked and for second time isn't work.:-( you could try this one too: Sub replacerev() What = InputBox("word to search") repl = InputBox("word to replace") Cells.replace What:=What, Replacement:=repl, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End Sub -- sisco98 "Louise" wrote: Thank you for your prompt replies. I'll give this a go. THanks again. Louise "sisco98" wrote: Hello Louise! I have just make something similar last week, maybe you can use this: sub replace () What = InputBox("word to search") repl = InputBox("word to replace") Sheets().Select Selection.Replace What:=What, Replacement:=repl, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End Sub -- sisco98 "Louise" wrote: Hi all Can anybody help? I have created very basic macros in Excel but have no knowledge whatsoever of Visual Basic. Is it possible to create a macro that will search for a particular word(s) in the whole of an Excel workbook and replace it with another? Would I simply go into Record mode and select Edit, Find & Replace and enter the text? How would this be applied to the whole workbook rather than the active sheet? Any help would be appreciated. Thank you Louise |
Your welcome! I'm happy about to managed to help you. By the way, I also
thoght that this will work automatically on all sheets without repeat it page by page. Bye, have a nice day! -- sisco98 "Louise" wrote: Thank you, once again!! This works fine. I didn't realise I would have to repeat it for each worksheet. Thanks again for all your help. Louise "sisco98" wrote: sorry, try this one. you should repeat the last part as many sheets you have, and of course if the name of your sheets are different, you should change them as well. if still not work, please contact me, i'm sure we can find some solution Sub rpl() Sheets("sheet1").Select What = InputBox("word to search") repl = InputBox("word to replace") Cells.Replace What:=What, Replacement:=repl, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Sheets("sheet2").Select Cells.Replace What:=What, Replacement:=repl, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Sheets("sheet3").Select Cells.Replace What:=What, Replacement:=repl, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End Sub cheers-- sisco98 "Louise" wrote: Hi Thanks again for your reply, however, I can still only get this to work on each individual sheet rather than the whole book?? Thanks. Louise "sisco98" wrote: Hi Louise, Your welcome. Sorry for the late answer, this macro works on all worksheet: Sub replaceallsheet() Worksheets.Select What = InputBox("word to search") repl = InputBox("word to replace") Cells.Replace What:=What, Replacement:=repl, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End Sub Gábor -- sisco98 "Louise" wrote: Thanks for this. I've tried this one and it works fine on individual worksheets, however, is there a way you can ask it to run on the whole workbook?? Thanks. Louise "sisco98" wrote: i've checked and for second time isn't work.:-( you could try this one too: Sub replacerev() What = InputBox("word to search") repl = InputBox("word to replace") Cells.replace What:=What, Replacement:=repl, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End Sub -- sisco98 "Louise" wrote: Thank you for your prompt replies. I'll give this a go. THanks again. Louise "sisco98" wrote: Hello Louise! I have just make something similar last week, maybe you can use this: sub replace () What = InputBox("word to search") repl = InputBox("word to replace") Sheets().Select Selection.Replace What:=What, Replacement:=repl, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End Sub -- sisco98 "Louise" wrote: Hi all Can anybody help? I have created very basic macros in Excel but have no knowledge whatsoever of Visual Basic. Is it possible to create a macro that will search for a particular word(s) in the whole of an Excel workbook and replace it with another? Would I simply go into Record mode and select Edit, Find & Replace and enter the text? How would this be applied to the whole workbook rather than the active sheet? Any help would be appreciated. Thank you Louise |
All times are GMT +1. The time now is 01:58 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com