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.
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.
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.
Hi,
try this:
Please mark this thread SOLVED if this solves your problem.![]()
Please Login or Register to view this content.
Regards
Please use [CODE]-TAGS
When your problem is solved mark the thread SOLVED
If an answer has helped you please clickto give reputation
Read the FORUM RULES
Hi jomili and tehneXus. Jomili, when I tried your code it stopped at- and I can't figure out why. Tehnexus, when I tried yours nothing was replaced - again, can't figure out why![]()
Please Login or Register to view this content.
Hi,
this: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.![]()
Please Login or Register to view this content.
Regards
did you replace the RepWhat and RepWith arrays with your replacementsAlso, 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.
![]()
Please Login or Register to view this content.
Hi tehnexus, strange but it still doesn't work for me...
Hi DKAbi,
could you upload an example workbook?
Regards
Hi again jomili, like this?
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.
![]()
Please Login or Register to view this content.
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.
Hi jomili,
as far as I can see the codedoes not make a difference as you are using the range object (line 3) to actually replace.![]()
Please Login or Register to view this content.
Regards
You're right; I didn't catch that. Revamped code below.![]()
Please Login or Register to view this content.
Here you go, both. Still can't get either code to work![]()
Thanks, Jomili. It works now
Hi,
run the code in the Sheet5 module, works here: BS Example.xls
Regards
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!
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.
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!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks