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
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - May 10, 2016 at 10:52 AM
Related:
- Replace function in Macro
- Find function on mac - Guide
- Spell number in excel without macro - Guide
- Accessor function c++ - Guide
- Spreadsheet function - Guide
- Agp function - Guide
3 responses
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
May 8, 2016 at 05:47 AM
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:-
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.
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.
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
May 9, 2016 at 05:08 AM
May 9, 2016 at 05:08 AM
Hello Smuneeb,
Here you go:-
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.
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
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
vcoolio
You are the hero.
Great Job
Also Thanks to Trowa
Keep me in good book
Regards
smuneeb
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
555
May 10, 2016 at 10:52 AM
May 10, 2016 at 10:52 AM
Indeed I'm overwhelmed with proudness!
And good to see it worked out for you Smuneeb.
And good to see it worked out for you Smuneeb.
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
May 10, 2016 at 03:34 AM
May 10, 2016 at 03:34 AM
Hello Smuneeb,
You're welcome. I'm glad that I was able to help.
Cheerio,
vcoolio.
You're welcome. I'm glad that I was able to help.
Cheerio,
vcoolio.
May 8, 2016 at 09:18 AM
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