I have macros, which search word "abc" in column D, the move this cell to column C:
I need the same only search range must be all active columns starting from D , e.g. D1:W5000. All found cells should move to column C.
Please Login or Register to view this content.
Last edited by sausis; 10-06-2015 at 10:30 AM.
Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.
Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.
Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here
(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
Hi Sausis,
what happens if you have more than one cell in the same row starting by "abc"
Captura.JPG
No, it will not happen, the data are unique and the length is not 3, but more, it is just example. So, thanks for good remark, but pls do not worry about it.
Last edited by sausis; 10-07-2015 at 02:11 AM.
OK
Assuming that "abc" does not repeat in any row, you can test the following code
Please Login or Register to view this content.
in case you have a duplicate in the same row, it will take just the first from left
Last edited by vichopalacios; 10-07-2015 at 12:43 PM.
Barriers are there for those who don't want to dream
If you don't have formulas in any of the cells in column c or the cells you are searching you could use this.
If you don't mind having the formulas replaced with their results, you could use this.
It will run very fast on large data sets.
If you have formulas and want to keep them in place, then don't use this.
Please Login or Register to view this content.
Last edited by skywriter; 10-07-2015 at 06:00 PM.
It will run very fast on large data sets.
Hi skywriter
I'm not used to work with large data sets, so...normaly I don't care about speed.
I'd like to learn why your idea of comparing inside the array is faster than comparing cells themselves.
As far as I can see, there is the main difference between both codes
Vicho
It was a good idea your aproach for lc and lr, as it covers the posibility of empty cells inside the db.
Hello Vicho,
Thank you very much. It works perfect.
Hello Skywriter,
Your code is to complicate for me, so I can not check why it is not working. It runs without errors, but nothing happens.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks