+ Reply to Thread
Results 1 to 16 of 16

Find and Replace Macro runs twice...how to avoid

  1. #1
    Registered User
    Join Date
    09-20-2016
    Location
    Frankfurt
    MS-Off Ver
    Mac 2016
    Posts
    26

    Find and Replace Macro runs twice...how to avoid

    Dear all

    I am using a find and replace macro to change country abbreviations to an internal code.
    Here's my problem though (please see code below).

    Columns("I").Replace What:="RU", Replacement:="1MOW"
    Columns("I").Replace What:="BE", Replacement:="1BRU"

    This is one example which messes things up for me.

    I want Belgium (BE) to change into 1BRU and Russia (RU) to change into 1MOW.
    However, once BE changes to BRU (now including the letters RU), the macro runs again and changes 1BRU to 1B1MOW.

    How can I fix that?

    Thank you!

  2. #2
    Forum Expert
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    ................
    Posts
    2,840

    Re: Find and Replace Macro runs twice...how to avoid

    Sounds like you have the code in a Change event...

    Try
    Please Login or Register  to view this content.
    at the start of the procedure and make sure to set True at the end.

  3. #3
    Registered User
    Join Date
    09-20-2016
    Location
    Frankfurt
    MS-Off Ver
    Mac 2016
    Posts
    26

    Re: Find and Replace Macro runs twice...how to avoid

    Unfortunately the result remains the same

  4. #4
    Forum Expert
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    ................
    Posts
    2,840

    Re: Find and Replace Macro runs twice...how to avoid

    OK - a short code snippet out of context like that means any replies are pure guesses.

    Post the full code.

  5. #5
    Registered User
    Join Date
    09-20-2016
    Location
    Frankfurt
    MS-Off Ver
    Mac 2016
    Posts
    26

    Re: Find and Replace Macro runs twice...how to avoid

    Quote Originally Posted by cytop View Post
    OK - a short code snippet out of context like that means any replies are pure guesses.

    Post the full code.
    Sub BuKr()
    '
    ' BuKr Macro
    ' Creates an extra column for BuKr
    '

    '
    Range("O1").Select
    ActiveCell.FormulaR1C1 = "BuKr"
    Range("P43").Select

    ' Transforming country abbreviations into BuKr

    Application.EnableEvents = False


    Columns("I").Replace What:="KZ", Replacement:="1ALA"
    Columns("I").Replace What:="JO", Replacement:="1AMM"
    Columns("I").Replace What:="NL", Replacement:="1AMS"
    Columns("I").Replace What:="TM", Replacement:="1ASB"
    Columns("I").Replace What:="AZ", Replacement:="1BAK"
    Columns("I").Replace What:="RS", Replacement:="1BEG"
    Columns("I").Replace What:="ID", Replacement:="1BEJ"
    Columns("I").Replace What:="BE", Replacement:="1BRU"
    Columns("I").Replace What:="HU", Replacement:="1BUD"
    Columns("I").Replace What:="AR", Replacement:="1BUE"
    Columns("I").Replace What:="RO", Replacement:="1BUH"
    Columns("I").Replace What:="LB", Replacement:="1BEY"
    Columns("I").Replace What:="EG", Replacement:="1CAI"
    Columns("I").Replace What:="DK", Replacement:="1CPH"
    Columns("I").Replace What:="QA", Replacement:="1DOH"
    Columns("I").Replace What:="AE", Replacement:="1DXB"
    Columns("I").Replace What:="IE", Replacement:="1DUB"
    Columns("I").Replace What:="FI", Replacement:="1HEL"
    Columns("I").Replace What:="UA", Replacement:="1IEV"
    Columns("I").Replace What:="TR", Replacement:="1IST"
    Columns("I").Replace What:="SA", Replacement:="1JED"
    Columns("I").Replace What:="ZA", Replacement:="1JNB"
    Columns("I").Replace What:="PT", Replacement:="1LIS"
    Columns("I").Replace What:="UK", Replacement:="1LON"
    Columns("I").Replace What:="NG", Replacement:="1LOS"
    Columns("I").Replace What:="LU", Replacement:="1LUX"
    Columns("I").Replace What:="ES", Replacement:="1MAD"
    Columns("I").Replace What:="OM", Replacement:="1MCT"
    Columns("I").Replace What:="IT", Replacement:="1MIL"
    Columns("I").Replace What:="RU", Replacement:="1MOW"
    Columns("I").Replace What:="BY", Replacement:="1MSQ"
    Columns("I").Replace What:="NO", Replacement:="1OSL"
    Columns("I").Replace What:="FR", Replacement:="1PAR"
    Columns("I").Replace What:="CZ", Replacement:="1PRG"
    Columns("I").Replace What:="LV", Replacement:="1RIX"
    Columns("I").Replace What:="BG", Replacement:="1SOF"
    Columns("I").Replace What:="BA", Replacement:="1SJJ"
    Columns("I").Replace What:="GQ", Replacement:="1SSG"
    Columns("I").Replace What:="SE", Replacement:="1STO"
    Columns("I").Replace What:="GE", Replacement:="1TBS"
    Columns("I").Replace What:="IR", Replacement:="1THR"
    Columns("I").Replace What:="AL", Replacement:="1TIA"
    Columns("I").Replace What:="EE", Replacement:="1TLL"
    Columns("I").Replace What:="IL", Replacement:="1TLV"
    Columns("I").Replace What:="AT", Replacement:="1VIE"
    Columns("I").Replace What:="LT", Replacement:="1VNO"
    Columns("I").Replace What:="PL", Replacement:="1WAW"
    Columns("I").Replace What:="HR", Replacement:="1ZAG"
    Columns("I").Replace What:="CH", Replacement:="1ZRH"

    Application.EnableEvents = True
    End Sub

  6. #6
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Re: Find and Replace Macro runs twice...how to avoid

    Hello
    instead of all these lines you can use similar UDF that will enable you to do these replacements
    All what you have to do is to type the old text in the array and the new text in the other array
    Have a look
    Please Login or Register  to view this content.
    < ----- Please click the little star * next to add reputation if my post helps you
    Visit Forum : From Here

  7. #7
    Registered User
    Join Date
    09-20-2016
    Location
    Frankfurt
    MS-Off Ver
    Mac 2016
    Posts
    26

    Re: Find and Replace Macro runs twice...how to avoid

    Quote Originally Posted by YasserKhalil View Post
    Hello
    instead of all these lines you can use similar UDF that will enable you to do these replacements
    All what you have to do is to type the old text in the array and the new text in the other array
    Have a look
    Please Login or Register  to view this content.
    Thanks. Yeah that Looks much easier on the eye...however does it fix my problem?

  8. #8
    Forum Expert
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    ................
    Posts
    2,840

    Re: Find and Replace Macro runs twice...how to avoid

    Probably not

    And there's nothing in the code you posted that would cause your problem either. How is the procedure BuKr called/started?

  9. #9
    Registered User
    Join Date
    09-20-2016
    Location
    Frankfurt
    MS-Off Ver
    Mac 2016
    Posts
    26

    Re: Find and Replace Macro runs twice...how to avoid

    Quote Originally Posted by cytop View Post
    Probably not

    And there's nothing in the code you posted that would cause your problem either. How is the procedure BuKr called/started?
    I'm not entirely sure what you mean "How is the procedure BuKr called/started? "

  10. #10
    Forum Expert
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    ................
    Posts
    2,840

    Re: Find and Replace Macro runs twice...how to avoid

    Exactly as I said... what starts that procedure. Is it called from another procedure? Worksheet change or other Event? User selecting the macro from the list of available macros...?

  11. #11
    Registered User
    Join Date
    09-20-2016
    Location
    Frankfurt
    MS-Off Ver
    Mac 2016
    Posts
    26

    Re: Find and Replace Macro runs twice...how to avoid

    Quote Originally Posted by cytop View Post
    Exactly as I said... what starts that procedure. Is it called from another procedure? Worksheet change or other Event? User selecting the macro from the list of available macros...?
    ah, ok...yeah the process is started by using the Macro menu and choosing the macro "BuKr"

  12. #12
    Forum Expert
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    ................
    Posts
    2,840

    Re: Find and Replace Macro runs twice...how to avoid

    Then there is absolutely nothing in the code you posted that would cause the issue you mentioned.

  13. #13
    Registered User
    Join Date
    09-20-2016
    Location
    Frankfurt
    MS-Off Ver
    Mac 2016
    Posts
    26

    Re: Find and Replace Macro runs twice...how to avoid

    Quote Originally Posted by cytop View Post
    Then there is absolutely nothing in the code you posted that would cause the issue you mentioned.
    strange but thanks for your help!

  14. #14
    Forum Expert
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    ................
    Posts
    2,840

    Re: Find and Replace Macro runs twice...how to avoid

    Post a sample workbook... I'd be interested to know the cause.

  15. #15
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Find and Replace Macro runs twice...how to avoid

    Hi,

    If the cells originally only contain the country code, all you need to do is add
    Please Login or Register  to view this content.
    to each line. If the codes are only part of the cell, you will have to be very careful about the order in which you replace.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  16. #16
    Registered User
    Join Date
    09-20-2016
    Location
    Frankfurt
    MS-Off Ver
    Mac 2016
    Posts
    26

    Re: Find and Replace Macro runs twice...how to avoid

    Quote Originally Posted by xlnitwit View Post
    Hi,

    If the cells originally only contain the country code, all you need to do is add
    Please Login or Register  to view this content.
    to each line. If the codes are only part of the cell, you will have to be very careful about the order in which you replace.
    Excellent, this did the trick! Thank you :D

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 08-24-2015, 08:10 AM
  2. Search and Find Macro runs too slow
    By Jietoh in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-13-2015, 11:25 AM
  3. Replies: 3
    Last Post: 11-25-2014, 06:08 AM
  4. [SOLVED] Excel Data > Filter > AutoFilter ? Successive runs of > Find > Replace
    By worswick25 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-08-2014, 05:06 PM
  5. Find last row and change it every time macro runs
    By tedy in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-28-2013, 08:45 AM
  6. Randomly Pair but avoid pairing again in future runs
    By allnet000 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-24-2013, 07:24 PM
  7. Find/Replace Macro Using Replace Table
    By LampCommandr in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-24-2011, 11:00 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1