Replace function in Macro [Solved/Closed]

Report
Posts
77
Registration date
Saturday September 5, 2015
Status
Member
Last seen
March 8, 2017
-
Posts
2669
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 15, 2020
-
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

3 replies

Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
213
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.
Posts
77
Registration date
Saturday September 5, 2015
Status
Member
Last seen
March 8, 2017
1
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
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
213
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.
Posts
77
Registration date
Saturday September 5, 2015
Status
Member
Last seen
March 8, 2017
1
Hi
vcoolio
You are the hero.
Great Job
Also Thanks to Trowa
Keep me in good book
Regards
smuneeb
Posts
2669
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 15, 2020
446
Indeed I'm overwhelmed with proudness!

And good to see it worked out for you Smuneeb.
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
213
Hello Smuneeb,

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

Cheerio,
vcoolio.