Report

Replace function in Macro [Solved]

Ask a question smuneeb 77Posts Saturday September 5, 2015Registration date March 8, 2017 Last seen - Last answered on May 10, 2016 at 10:52 AM by TrowaD
Hello,

I would like to request that in the range D11 to D510 if "-" or " -" or "- " this found
then it would be replace with " ".
Please do it through macro for all sheets

Thanks

See more 
Helpful
+0
plus moins
Hello Smuneeb,

I believe that TrowaD has already coverd this for you here:-

http://ccm.net/forum/affich-892160-find-and-replace-with-condition

However, I see that you have some additional conditions to be met, so a slight adjustment to Trowa's code, as follows, should do the task for you:-


Sub RunMe()

    Dim ws As Worksheet
    Dim ar As Variant
ar = Array("-", " -", "- ")

For Each ws In Worksheets
    ws.Range("D11:D510").Replace What:=ar, Replacement:=vbNullString, _
    LookAt:=xlPart, MatchCase:=False
Next

End Sub


As you can probably see, we are just using an array to include all of the conditions.

It would be a good idea if you could give us an example of how you are actually using the text in Column D with the conditions you wish to replace as I feel that you may have further questions to ask.

I hope that this helps.

Cheerio,
vcoolio.
smuneeb 77Posts Saturday September 5, 2015Registration date March 8, 2017 Last seen - May 8, 2016 at 09:18 AM
Hi vcoolio
First of all I would appreciate for your polite and positive response.
Yes there are more queries to ask.
1. If the Name is entered as Kamal-ud-Din then Replacement will work in this Manner that it gives this output Kamal ud Din.
2. If the Name is entered as Kamal -ud- Din then Replacement will work in this Manner that it gives this output Kamal ud Din.
3. If the Name is entered as Kamal- ud -Din then Replacement will work in this Manner that it give this output Kamal ud Din.
I think I have cleared the point.
Thanls
Reply
Leave a comment
Helpful
+0
plus moins
Hello Smuneeb,

Here you go:-


Sub RunMe()

    Dim ws As Worksheet
    Dim ar As Variant
ar = Array(" -", "- ")

For Each ws In Worksheets
    ws.Range("D11:D510").Replace What:="-", Replacement:=" "
    ws.Range("D11:D510").Replace What:="  ", Replacement:=" "
    ws.Range("D11:D510").Replace What:=ar, Replacement:=vbNullString, _
    LookAt:=xlPart, MatchCase:=False
Next

End Sub


Following is the link to my test work book for you to peruse:-
https://www.dropbox.com/s/kz550ag5m8ci7in/Smuneeb%28Find%20%26%20Replace%20function%29.xlsm?dl=0

It has three sheets with some data as per your sample above.

Trowa will be proud!

Cheerio,
vcoolio.
smuneeb 77Posts Saturday September 5, 2015Registration date March 8, 2017 Last seen - May 9, 2016 at 02:06 PM
Hi
vcoolio
You are the hero.
Great Job
Also Thanks to Trowa
Keep me in good book
Regards
smuneeb
Reply
TrowaD 2165Posts Sunday September 12, 2010Registration date ModeratorStatus April 25, 2017 Last seen - May 10, 2016 at 10:52 AM
Indeed I'm overwhelmed with proudness!

And good to see it worked out for you Smuneeb.
Reply
Leave a comment
Helpful
+0
plus moins
Hello Smuneeb,

You're welcome. I'm glad that I was able to help.

Cheerio,
vcoolio.
Leave a comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!