+ Reply to Thread
Results 1 to 18 of 18

Optimization of Replace macro

  1. #1
    Registered User
    Join Date
    11-15-2012
    Location
    Denmark
    MS-Off Ver
    Excel 2021
    Posts
    50

    Optimization of Replace macro

    I have the macro below which works as intended... but... the code is very long. Is there a way to optimize it, using fewer code lines? Any help is appreciated!

    Please Login or Register  to view this content.

  2. #2
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    4,046

    Re: Optimization of Replace macro

    Without wading into yours, see if the below little macro would do what you need (just replace the values in the arrays):
    Please Login or Register  to view this content.

  3. #3
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

    Re: Optimization of Replace macro

    Hi,

    try this:
    Please Login or Register  to view this content.
    Please mark this thread SOLVED if this solves your problem.

    Regards
    Please use [CODE]-TAGS
    When your problem is solved mark the thread SOLVED
    If an answer has helped you please click to give reputation
    Read the FORUM RULES

  4. #4
    Registered User
    Join Date
    11-15-2012
    Location
    Denmark
    MS-Off Ver
    Excel 2021
    Posts
    50

    Re: Optimization of Replace macro

    Hi jomili and tehneXus. Jomili, when I tried your code it stopped at
    Please Login or Register  to view this content.
    - and I can't figure out why. Tehnexus, when I tried yours nothing was replaced - again, can't figure out why

  5. #5
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

    Re: Optimization of Replace macro

    Hi,

    this:
    Please Login or Register  to view this content.
    works fine for me. Please be sure to have the data to be replaced in column "BS" of the worksheet "CBO Data" as you did it in the first post.

    Regards

  6. #6
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    4,046

    Re: Optimization of Replace macro

    did you replace the RepWhat and RepWith arrays with your replacements
    Please Login or Register  to view this content.
    Also, the code I posted works on the whole active sheet. If you only want to replace in column BS, add these lines before and after:
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    11-15-2012
    Location
    Denmark
    MS-Off Ver
    Excel 2021
    Posts
    50

    Re: Optimization of Replace macro

    Hi tehnexus, strange but it still doesn't work for me...

  8. #8
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

    Re: Optimization of Replace macro

    Hi DKAbi,

    could you upload an example workbook?

    Regards

  9. #9
    Registered User
    Join Date
    11-15-2012
    Location
    Denmark
    MS-Off Ver
    Excel 2021
    Posts
    50

    Re: Optimization of Replace macro

    Hi again jomili, like this?

    Please Login or Register  to view this content.
    Do I need to have a RepWith for each RepWhat, so the two must match? Also, where in the code should I put
    Please Login or Register  to view this content.
    and the
    Please Login or Register  to view this content.

  10. #10
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    4,046

    Re: Optimization of Replace macro

    I placed the End With where it should be, you already had the With part (every WITH needs an END WITH). On the RepWhat and RepWiths, it's a one to one relationship (a RepWith for every RepWhat, like you've done it below):
    Please Login or Register  to view this content.
    Last edited by jomili; 04-18-2013 at 04:34 PM.

  11. #11
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

    Re: Optimization of Replace macro

    Hi jomili,

    as far as I can see the code
    Please Login or Register  to view this content.
    does not make a difference as you are using the range object (line 3) to actually replace.

    Regards

  12. #12
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    4,046

    Re: Optimization of Replace macro

    You're right; I didn't catch that. Revamped code below.
    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    11-15-2012
    Location
    Denmark
    MS-Off Ver
    Excel 2021
    Posts
    50

    Re: Optimization of Replace macro

    Here you go, both. Still can't get either code to work
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    11-15-2012
    Location
    Denmark
    MS-Off Ver
    Excel 2021
    Posts
    50

    Thumbs up Re: Optimization of Replace macro

    Thanks, Jomili. It works now

  15. #15
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

    Re: Optimization of Replace macro

    Hi,

    run the code in the Sheet5 module, works here: BS Example.xls

    Regards

  16. #16
    Registered User
    Join Date
    11-15-2012
    Location
    Denmark
    MS-Off Ver
    Excel 2021
    Posts
    50

    Re: Optimization of Replace macro

    I must be going mad. When I tried with a couple of examples more, the code stopped again. Jomili, could you test against the example I uploaded? Thank you both for your help. Appreciate it!

  17. #17
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    4,046

    Re: Optimization of Replace macro

    Yep, you're going mad. I downloaded your last example, ran the code, and it worked just fine. Remember, as the code is presently set up it only replaces 4 values; you haven't completed your arrays to use all of the examples. And the current replacement is "", or "nothing", so the effect is that the array items are deleted and replaced with nothing.

    I've attached your example again. Copy the cells from BR into BS, and run the code. You'll see the first 4 items disappear. You can copy from BR to BS over and over again to text your macro as you populate more of the array. Let me know if that works for you.
    Attached Files Attached Files

  18. #18
    Registered User
    Join Date
    11-15-2012
    Location
    Denmark
    MS-Off Ver
    Excel 2021
    Posts
    50

    Re: Optimization of Replace macro

    Thank you, both. They both work beautifully now.... eh... but I have a confession to make. I realized that the reason why no names were replaced to begin with was because of the 'XXX'. Apparently, it worked in a normal find and replace, but as soon as I replaced that with the true value, no more problems. Sorry for taking up extra time - and again: thank you!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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