Report

Replace function in Macro [Solved]

Ask a question smuneeb 76Posts Saturday September 5, 2015Registration date October 12, 2016 Last seen - Last answered 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 76Posts Saturday September 5, 2015Registration date October 12, 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 76Posts Saturday September 5, 2015Registration date October 12, 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 2032Posts Sunday September 12, 2010Registration date ModeratorStatus December 1, 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

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!