Hello,
I was curious if there is a way to control find on a page and select all nonadjacent cells containing specific text and then while selected copy paste value?
Hello,
I was curious if there is a way to control find on a page and select all nonadjacent cells containing specific text and then while selected copy paste value?
What does specific text mean? One, two, three, four words? Or sentences?
Paste to where? Same sheet, another sheet, another workbook?
Last edited by jolivanes; 01-16-2018 at 12:45 PM. Reason: spelling mistake
So, if an adjacent cell contains that text do nothing?
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)
You can't do one thing. XLAdept
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin
so I want to hard code all formulas that contain the text HFM. I don't want to move them at all. I want to select them all at once and copy paste values in the cells that they currently sit in. The actual file I have has confidential information so I would have to doctor one up to get it to you as an example.
What about the adjacent cells?
Great idea...do just that if you would like a solution...I would have to doctor one up to get it to you as an example
Good Luck
I don't presume to know what I am doing, however, just like you, I too started somewhere...
One-day, One-problem at a time!!!
If you feel I have helped, please click on the star to left of post [Add Reputation]
Also....add a comment if you like!!!!
And remember...Mark Thread as Solved.
Excel Forum Rocks!!!
The attached file is an example. What I would want in this example is to Select all of the cells with LEFT in it and copy paste value. In the live document the text would be HFM.
Maybe:
Please Login or Register to view this content.
Last edited by xladept; 01-16-2018 at 02:02 PM. Reason: Paste Actual Value
Something like this maybe. Change references as required.
AFAIK, you'll have to loop anyway in order to get the cells that qualify into a Union of cells.
Might be wrong though.
I assume you know to always test supplied code on a copy of your original workbook.
Please Login or Register to view this content.
This is more to the point I think after looking at your example.
Please Login or Register to view this content.
Last edited by jolivanes; 01-16-2018 at 02:04 PM. Reason: code tags
Thanks for the code. It doesn't seem to be working however. See the attached. I dropped your code in and change HFM to LEFT to see if it works and it still keeps all of the formulas.
xladept...your code worked. Thanks for all of your help!
Sorry, I misunderstood all this time. I was thinking about the value in the cell.
My apologies
You're welcome!
Since I was working on it anyway, these two should work also, although I know you're happy with what you have.
Possibly for future use, who knows.
Please Login or Register to view this content.
Please Login or Register to view this content.
In case the U.Value shouldn't be all the same value, this modification will get each one:
Please Login or Register to view this content.
@ xladept
I have been trying to change your contribution. I hope you don't mind
I was wondering if your code can be changed to SpecialCells(xlCellTypeFormulas) there where you have ws.Cells.Find(n)
I tried a few things but no cigar.
Don't spend any of your precious time on it but if you happen to know, I would be thankful to hear.
Regards
@ jolivanes - I don't know offhand, I'd have to do trial and error but maybe:
Please Login or Register to view this content.
Last edited by xladept; 01-16-2018 at 06:25 PM.
Thank you very much. I'll play with that for a while.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks