Closed Thread
Results 1 to 18 of 18

Creating a macro to find and replace text

  1. #1
    Louise
    Guest

    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

  2. #2
    sisco98
    Guest

    RE: Creating a macro to find and replace text

    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


  3. #3
    Martin
    Guest

    RE: Creating a macro to find and replace text

    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


  4. #4
    Louise
    Guest

    RE: Creating a macro to find and replace text

    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


  5. #5
    sisco98
    Guest

    RE: Creating a macro to find and replace text

    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


  6. #6
    Louise
    Guest

    RE: Creating a macro to find and replace text

    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


  7. #7
    sisco98
    Guest

    RE: Creating a macro to find and replace text

    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


  8. #8
    Louise
    Guest

    RE: Creating a macro to find and replace text

    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


  9. #9
    sisco98
    Guest

    RE: Creating a macro to find and replace text

    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


  10. #10
    Louise
    Guest

    RE: Creating a macro to find and replace text

    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


  11. #11
    sisco98
    Guest

    RE: Creating a macro to find and replace text

    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


  12. #12
    Registered User
    Join Date
    01-29-2007
    Posts
    47

    Find Macro

    Hello,

    I'm using this macro to use the find and replace feature, but it doesn't find anything when I enter text into the search box. Can someone help please. Thanks

    Sub FindData1ReplaceData3()

    Sheets("Master List").Select
    What = InputBox("SDE to Search")
    What = InputBox("Search Again or Cancel if Finished")
    Cells.Replace What:=What, Replacement:=repl, LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
    Sheets("SDE Request").Select

    End Sub

  13. #13
    Registered User
    Join Date
    04-01-2009
    Location
    Dallas Texas
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Creating a macro to find and replace text

    Hello,
    I just ran across this tread again.

    I tried the Macro listed here and it works. now the only thing I want to do now is to have this macro find and replace data in another macro? How could I go about doing that. I know that I would have to use it with the Extensivility Library but how can I translate that into that and use this? or do I even need to go that far?

    Thanks
    Vaughn

  14. #14
    Registered User
    Join Date
    03-15-2010
    Location
    Vancouver, BC
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Creating a macro to find and replace text

    This seems utterly useless, same as using the search and replace feature in the menu. If it was automated somehow I could see it being useful but still requires user input. Why is that any different than using CTRL-H
    Last edited by Geoffww; 03-15-2010 at 05:33 PM.

  15. #15
    Registered User
    Join Date
    09-05-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Creating a macro to find and replace text

    This code works for me...Instead of repeating same code for every worksheets..i used For loop..

    Please Login or Register  to view this content.
    Thanks so much:-)
    Last edited by arlu1201; 01-23-2013 at 05:14 AM.

  16. #16
    Administrator arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,147

    Re: Creating a macro to find and replace text

    RG12,

    Welcome to the forum.

    I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. If you need more information on how to use them, check my signature below this post.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  17. #17
    Registered User
    Join Date
    04-11-2014
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Creating a macro to find and replace text

    While using the macro for find and replacement I'm facing one problem.The replaced word is changing all the format of the content in that cell.Can any one help in fixing this.

  18. #18
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Creating a macro to find and replace text

    This is a 9 years old thread!!

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1