On Sheet2 in column B from row 3 downwards there are two words. These words can vary but it will always be two words. So from B3 downwards the cell would have to contain for example either "Test" or "Active". As I said, the two words can be anything. The column will always be sorted from A to Z so what I need is for a macro to copy from B3 down as far as Bx and paste this into sheet 1 in A1. And then to copy from B(x+1) down as far as B55 and paste it into sheet 1 in B1.
Would be easier to help if you attach a workbook, and show what your expected outcome looks like.
let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source
If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE
Walking the tightrope between genius and eejit...
Hi, ScabbyDog,
can you elaborate on Bx and on B(x+1)/B55 or attach a sample to make clear? The situation you made up counts for 53 rows, and I donīt see the difference between the pasting into Column A and Column B from the same range.
Ciao,
Holger
Use Code-Tags for showing your code: [code] Your Code here [/code]
Please mark your question Solved if there has been offered a solution that works fine for you
Maybe:
Please Login or Register to view this content.
Problem with this John is that it will be different words all the same and not always Test and Active.Originally Posted by JOHN H. DAVIS
Bx is the last cell where the first word is used. So if cells B3 to B23 for example all contained LOVE then Bx would be cell B23
What determines which two words to use?
They are imported from another sheet. They will always be different words each time the macro is run, but it will only ever be two unique words.
Are they imported to a certain range?
Yeah in range B3:B55 but they will also be data sorted so the first say 20 cells could be the first word and the next 15 the second word and the remaining cells in the range blank. Or it could be the first 25 cells the first word, the next 15 cells the 2nd word, and the remaining cells blank, etc etc etc
Another way of wording what I want would be:
I want to copy from B3 down as far to where the cell below it does not equal B3 and paste it to sheet 1 in cell A1. And then, copy the cell that does not equal B3 down as far as B55 and paste it into sheet1 cell B1
In order to use this you have to assume that the top value (B3) is one of the words, and the bottom value (B55) is the other. Can't be sure though, since there are other words that may end up in those positions based on your sort?
Please Login or Register to view this content.
B3 will always be the first word. B55 won't always be the 2nd word though. There will only ever be two words in the range B3:B55.
Untested - Maybe:
Please Login or Register to view this content.
That works, BUT it always fails to copy the very last cell where the first word exists. So if B20=Test and B21=OtherWord it would not copy that last line.
Maybe:
Please Login or Register to view this content.
Also, how would I alter it so that it starts pasting in B28 onwards for the second loop? That would be it solved then.
Last edited by ScabbyDog; 12-11-2013 at 03:03 PM.
Maybe:
Please Login or Register to view this content.
Hi, ScabbyDog,
maybe like this:
Ciao,Please Login or Register to view this content.
Holger
Thanks guys. Working a treat now.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks