Replace function in Macro

Solved/Closed
smuneeb Posts 67 Registration date Saturday September 5, 2015 Status Member Last seen March 8, 2017 - May 8, 2016 at 01:45 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - May 10, 2016 at 10:52 AM
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

Related:

3 responses

vcoolio Posts 1409 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 262
May 8, 2016 at 05:47 AM
Hello Smuneeb,

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

https://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 Posts 67 Registration date Saturday September 5, 2015 Status Member Last seen March 8, 2017 1
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
0
vcoolio Posts 1409 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 262
May 9, 2016 at 05:08 AM
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 Posts 67 Registration date Saturday September 5, 2015 Status Member Last seen March 8, 2017 1
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
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
May 10, 2016 at 10:52 AM
Indeed I'm overwhelmed with proudness!

And good to see it worked out for you Smuneeb.
0
vcoolio Posts 1409 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 262
May 10, 2016 at 03:34 AM
Hello Smuneeb,

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

Cheerio,
vcoolio.