Results 1 to 9 of 9

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
    26,998

    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
    68,094

    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
    240

    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
    New Member
    Join Date
    Jan 2014
    Posts
    14

    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

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