How to automatically increment next word in a cell down 1 each time

  • Hi All

    Is there a way for the following

    In the temp attached file is on the Sheet 1 when I enter a random word

    in cell C6, It automatically transfers to sheet 2 say in column D6 and the

    next word is added BELOW in D7 the next is added to cell D8 below the

    previous word and so on.

    What I am trying to achieve is when I change the word in sheet 1, C6 lets say, the last word was

    " This " was moved to Sheet2 cell D6, the next word is " Now ", it checks what is in D6 so if the new

    word is different to the cell D6 , it is moved to Sheet 2 Cell D7, then the next new word checks to

    see what the previous cell in this case the word in sheet 2 cell D7, and so on.

    Basically I am trying to create a dictionary of the words being used in the sheet.

    Many thanks in advance


    Posted elsewhere:-…ach-time.html#post5335295

    Temp File.xlsmTemp File.xlsm

  • Good afternoon Justin

    What I have in mind is to create a word finder template for the wife, and what I am doing is to set up a matrix of squares 26x26 and i n the various cells I will create a random word, once I have completed each page i have to create a listing on the top of each page of ALL the words used so she can cross them out as she finds them.

    Having achieved all of that I wish to make up a listing of each word used, so as I add a word ( say for arguments sake ) into Sheet 3 which is now all cells are in black and white the master sheet each new word is coloured in a different colour so I can see what is where,

    On sheet 3 cell AD5 I add a word from the listing that word gets automatically moved to Sheet 4 Cell C4, and when I change the word on sheet 3 to a new one it gets added below Sheet 4 Cell c4 down to cell C5 and so on thereby creating a listing of all the words used.

    I trust this long winded explanation may help.

    Temp File 2.xlsmTemp File 2.xlsm

  • I think you want a worksheet change event placed in the code of for the sheet, try this one:

    This transfers the word to the list in column C of sheet4 (whose code name is sheet3) after you hit enter on the cell AD6 in sheet3 (or whichever sheet you add the code to).

  • I added functionality to ensure it is a unique list:

    The "option compare text" line needs to go at the top of the page and is separate to the sub. This line means it will ignore the case eg Big, big and BiG will be seen as the same, if you do not want this remove the line.

  • Thank you for that works well

    I am doing all this with one eye as I have severe cataracts in both eyes and most left eye is useless,

    At last the Local Government has allowed hospitals to start surgery again, I get mine done on the 1st and 4th of June

    I am hoping for better sight ?????

    Now back to coding how much code would be involved in in duplicating the data from the sheet 4 into sheet 3 cells B1 to AA5 with a space inbetween each word, if it is too much then I will make a macro.

    Ohhh where are you located please.


  • Hi Pete,

    Sorry I missed the message regarding your eye and surgery, my aunt just had that done and it has worked great! hope you get 20/20! I am in Melbourne Australia.

    I will have a look at the question you included there tomorrow, feel free to send me a message to clarify and good luck with the surgery.

    Use the code here, I just noticed the code you are using is the original I posted which I changed. It is apparently best to avoid exiting a loop as I did originally.