Report

Replace function in Macro [Solved]

Ask a question smuneeb 74Posts Saturday September 5, 2015Registration date June 3, 2016 Last seen - Latest answer on May 10, 2016 10:52AM
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
moins plus
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 74Posts Saturday September 5, 2015Registration date June 3, 2016 Last seen - May 8, 2016 09:18AM
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
Add comment
Helpful
+0
moins plus
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 74Posts Saturday September 5, 2015Registration date June 3, 2016 Last seen - May 9, 2016 02:06PM
Hi
vcoolio
You are the hero.
Great Job
Also Thanks to Trowa
Keep me in good book
Regards
smuneeb
Reply
TrowaD 1989Posts Sunday September 12, 2010Registration date ModeratorStatus September 27, 2016 Last seen - May 10, 2016 10:52AM
Indeed I'm overwhelmed with proudness!

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

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

Cheerio,
vcoolio.
Add comment

Members get more answers than anonymous users.

Being a member gives you detailed monitoring of your requests.

Being a member gives you additional options.

Not a member yet?

sign-up, it takes less than a minute and it's free!