VBA find and replace ?

[mikeprice53]

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

 

[jimboy]

Why don't you record a macro doing this?

 

[Joe4]

Simply record a macro doing it manually. That will give you the code you need to place in your VBA code.

 

[mikeprice53]

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)

 

[Andrew Poulsom]

You mean like this?

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.

 

[mikeprice53]

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

Will try this way !

Thank you Andrew

 

[mikeprice53]

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 !

 

[needhelp2]

Dear Andrew, How do we manage same script specially replacement:"" to pick data from cell a11?
i have tried but its not working.

Selection.Replace What:="*a*", Replacement:="activecell.value="a11", LookAt:=xlPart, _         SearchOrder:=xlByRows, MatchCase:=False

can you help me in this regard?

Smith
</pre>

 

[Memon]

Dear Andrew, How do we manage same script specially replacement:"" to pick data from cell a11?
i have tried but its not working.

Selection.Replace What:="*a*", Replacement:="activecell.value="a11", LookAt:=xlPart, _         SearchOrder:=xlByRows, MatchCase:=False

can you help me in this regard?

Smith

Selection.Replace What:="*a*", Replacement:=range("a11"),  LookAt:=xlPart, _         SearchOrder:=xlByRows, MatchCase:=False

Iknow it's pld post but it may help someone esle

 

[BrewMAJ]

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.

You mean like this?

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.