Results 1 to 10 of 10

VBA find and replace ?

This is a discussion on VBA find and replace ? within the Excel Questions forums, part of the Question Forums category; I am trying to find text using vba method and replace with nothing to wipe out the value. there are ...

  1. #1
    Board Regular
    Join Date
    Jan 2003
    Posts
    297

    Default VBA find and replace ?

    I am trying to find text using vba method and replace with nothing to wipe out the value.

    there are other things already in the cell so thats why I need to find and relace but I need to do it with a button hence hte VBA way !

    Please help

    Thanks

  2. #2
    Board Regular
    Join Date
    Apr 2002
    Posts
    2,317

    Default Re: VBA find and replace ?

    Why don't you record a macro doing this?

  3. #3
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    29,668

    Default Re: VBA find and replace ?

    Simply record a macro doing it manually. That will give you the code you need to place in your VBA code.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  4. #4
    Board Regular
    Join Date
    Jan 2003
    Posts
    297

    Default Re: VBA find and replace ?

    good idea !

    must ave been the 2 pints I had at lunch that is stopping me from thinking the easy method ! :0)

    Like trying to stand up in a rowing boat huh !

    Thanks :0)

  5. #5
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    71,742

    Default Re: VBA find and replace ?

    You mean like this?

    Code:
    Sub Macro5()
    '
    ' Macro5 Macro
    ' Macro recorded 07/11/2003 by APoulsom
    '
    
    '
        Range("A1:A10").Select
        Selection.Replace What:="*a*", Replacement:="", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False
    End Sub
    It looks for the letter a anywhere in the text and wipes out the whole cell.

  6. #6
    Board Regular
    Join Date
    Jan 2003
    Posts
    297

    Default Re: VBA find and replace ?

    ooh thanks - just tried the macro way and didnt work !

    Will try this way !

    Thank you Andrew

  7. #7
    Board Regular
    Join Date
    Jan 2003
    Posts
    297

    Default Re: VBA find and replace ?

    Thats excellent thank you once again Andrew ! I managed to change your code to fit mine in and it works awsomely ! :0)

    Hope some day I can repay the favour or at elast pass on my little knowledge to help others. Ta !

  8. #8
    Board Regular
    Join Date
    Apr 2011
    Posts
    249

    Default Re: VBA find and replace ?

    Dear Andrew, How do we manage same script specially replacement:"" to pick data from cell a11?
    i have tried but its not working.
    Code:
    Selection.Replace What:="*a*", Replacement:="activecell.value="a11", LookAt:=xlPart, _         SearchOrder:=xlByRows, MatchCase:=False
    can you help me in this regard?

    Smith

  9. #9
    Board Regular
    Join Date
    Jan 2014
    Posts
    52

    Default Re: VBA find and replace ?

    Quote Originally Posted by needhelp2 View Post
    Dear Andrew, How do we manage same script specially replacement:"" to pick data from cell a11?
    i have tried but its not working.
    Code:
    Selection.Replace What:="*a*", Replacement:="activecell.value="a11", LookAt:=xlPart, _         SearchOrder:=xlByRows, MatchCase:=False
    can you help me in this regard?

    Smith
    Code:
    Selection.Replace What:="*a*", Replacement:=range("a11"),  LookAt:=xlPart, _         SearchOrder:=xlByRows, MatchCase:=False
    Iknow it's pld post but it may help someone esle

  10. #10
    New Member
    Join Date
    Jun 2015
    Location
    Phoenix, AZ
    Posts
    1

    Default Re: VBA find and replace ?

    I realize this thread is pretty old, but my question may still apply.

    I've been using that standard search and replace successfully, except when, just previously, I did a Ctrl-h from the Excel S&R (options>within) and selected workbook instead of sheet. After that, my vb script will fail. It attempts to also search and replace the whole workbook, over riding my selection. The script will only do the first row and quit. Not only does it do the first row, but it does columns outside of my selected range. I'd like to modify my script to clear whatever parameter is over riding my range selection.

    Quote Originally Posted by Andrew Poulsom View Post
    You mean like this?

    Code:
    Sub Macro5()
    '
    ' Macro5 Macro
    ' Macro recorded 07/11/2003 by APoulsom
    '
    
    '
        Range("A1:A10").Select
        Selection.Replace What:="*a*", Replacement:="", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False
    End Sub
    It looks for the letter a anywhere in the text and wipes out the whole cell.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com