+ Reply to Thread
Results 1 to 12 of 12

Move a1 to a2 and a2 to a3.

  1. #1
    Registered User
    Join Date
    08-21-2007
    Posts
    13

    Move a1 to a2 and a2 to a3.

    Could anyone please help me.
    I want to enter a number 0 to 999 in cell A1 and when the enter key is pressed move the contents of cell A1 to A2 and A2 to A3, cascading down until A40. So after five numbers the first number I entered woul now have cascaded down to cell A5 ready to go into cell A6.
    Would I have to use some kind of program or does excel have a command for this?

    Thank you in advance.

  2. #2
    Registered User
    Join Date
    08-21-2007
    Posts
    2
    Can you be a bit more descriptive. I have no idea what you are referring to.

  3. #3
    Forum Contributor harrywaldron's Avatar
    Join Date
    05-24-2007
    Location
    Roanoke, VA
    MS-Off Ver
    Office Professional 2010 BETA
    Posts
    169
    If you enter a "1" in A1, do you want "2" in A2, "3" in A3, etc ...

    One way of doing this is to enter the formula of =(A1+1) into A2 and copy this formula down the desired range of 40 cells

  4. #4
    Registered User
    Join Date
    08-21-2007
    Posts
    13
    Numbers will be given at random from 1 to 999. eg 19,832,248,77 approx 40 at a time.
    This is what I want to do. We will just use four numbers to hopefully make it easier to describe.

    Have cell A1 as the main data input area and display it in large easily visible sized characters. And have the other cells about 16point size.

    We type in 19 the press enter.
    A5 contents are now transferred to another store area out of site.
    A4 is moved into A5 and overwrites anything in A5
    A3 is moved into A4 and overwrites anything in A4
    A2 is moved into A3 and overwrites anything in A3
    A1 is moved into A2 and overwrites anything in A2
    We type in 832 the press enter.
    A5 contents are now transferred to another store area out of site.
    A4 is moved into A5 and overwrites anything in A5
    A3 is moved into A4 and overwrites anything in A4
    A2 is moved into A3 and overwrites anything in A3
    A1 is moved into A2 and overwrites anything in A2
    We type in 248 the press enter.
    A5 contents are now transferred to another store area out of site.
    A4 is moved into A5 and overwrites anything in A5
    A3 is moved into A4 and overwrites anything in A4
    A2 is moved into A3 and overwrites anything in A3
    A1 is moved into A2 and overwrites anything in A2
    We type in 77 the press enter.
    A5 contents are now transferred to another store area out of site.
    A4 is moved into A5 and overwrites anything in A5
    A3 is moved into A4 and overwrites anything in A4
    A2 is moved into A3 and overwrites anything in A3
    A1 is moved into A2 and overwrites anything in A2

    The store area where cell A5 gets transferred to will be in another area on the sheet. We just want to see the last four numbers we entered to aid error checking whilst manual data entry is in progress. That is why we want the data entry cell to be larger , to aid data entry and the others are in a smaller font because they are only there to have a quick scan of the last few numbers entered.

  5. #5
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467
    Hi UK,

    I think the attached VBA code will do what you want as far as updating the four cells is concerned. You should insert it into the VBA module FOR THE WORKSHEET CONTAINING THE DATA - right-click on the worksheet tab & select "View Code"

    Please Login or Register  to view this content.
    You can edit the following line to suit the address of the cell into which you will be entering the data:
    Please Login or Register  to view this content.
    As far as transferring the data from the fifth cell is concerned, you'll have to give me some more information. The value will need to be entered automatically (using VBA code) into its new destination - i.e. it will not be "moved" using formulas etc., so please let me know where it needs to be stored (e.g. at the end of another list? - note, placing it at the end of a list is slightly less involved than placing it at the beginning.)

    Hope this is of some assistance to you - please let me know how you get on.

    regards,

    Greg M

  6. #6
    Registered User
    Join Date
    08-21-2007
    Posts
    13
    Greg M thanks for your help.

    It did exactly want I wanted, one problem I am having is the entry cursor goes down to cell A2 when I press enter and I have to up arrow it back to cell A1.

    Is there an automatic way of putting it back to A1.

    Thank you

  7. #7
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467
    Hi again,

    Glad this has helped you.

    Sure - the "Move selection after Enter" property can be useful at times & very irritating at others. To disable this use Tools > Options > View, and untick the "Move selection after Enter" box. Note that this change will apply to ALL workbooks, not just the one we're working on.

    If you want to retain this feature in general, but to effectively disable it when working in the data entry cell, then add the following line of code between the last "EndIf" statement & the "EnableEvents:" label:
    Please Login or Register  to view this content.
    Best regards,

    Greg M

  8. #8
    Registered User
    Join Date
    08-21-2007
    Posts
    13
    If I use INSERT, userform from the menu in the VBA editor I can create a gui which just has the Input box(textbox1) and underneath that I have four smaller boxes displayed from left to right. These would display the contents of A2 to A5.
    I do not want the rows and columns displayed as in the standard spreadsheet, the above display would be better for this purpose and is less cluttered.

    Can this userform be added to the code above.
    Attached Files Attached Files
    Last edited by ukmxer; 08-22-2007 at 03:38 PM.

  9. #9
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467
    Hi again,

    I've incorporated your data entry form into the workbook.

    Hope this helps - please let me know how you get on.

    Regards,

    Greg M
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    08-21-2007
    Posts
    13
    GREG_M excellent, absolutley bang on.
    Worked first time and did exactly what I wanted.
    I will now try to understand how you did it by working through your code, but to be honest I am well and truly struggling.

  11. #11
    Registered User
    Join Date
    08-21-2007
    Posts
    13
    Greg when the code runs, the userform gets to this line:-
    If Me.txtA1.Value <> "" Then
    Range("A1").Value = Me.txtA1.Value

    and it jumps to the code on sheet1 and does all the cell shifting.

    I have been stepping through using the debug commands and now I have problems.
    How does it know where to go from the userform window and when it finishes the worksheet code it jumps back to the userform. HOW does it know?

    I am sorry if this is easy for everyone else but this is my first proper hands on and I am on my own here doing my best to work out how it works.

  12. #12
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467
    Hi again,

    No problem - this isn't really "beginner-level" code! I'll try to explain how it works.

    The significant thing is that the "Worksheet_Change" routine on Sheet1 is an example of event-driven code. There are many operations in Excel which are known as "events", e.g. saving a workbook, adding a worksheet, selecting a cell on a worksheet etc. etc. ... When an "event" occurs, Excel recognises it as such & looks for any code which the user wants to execute when that specific "event" occurs. If no code has been written, Excel just carries on doing what it normally does.

    In our case the "event" we're using is the "Worksheet Change" event - i.e. whenever the user changes the value in any cell on the worksheet, any code written for the "Worksheet_Change" routine is executed. (Note, these are changes made by the USER, not changes resulting from updating formulas etc. - those changes trigger a different "event", but that's another day's work)

    So, when the "Range("A1").Value = Me.txtA1.Value" statement is executed, the value in cell A1 changes & the "Worksheet_Change" routine is triggered. This executes the updating process for cells A2:A5.

    When the "Worksheet_Change" routine is completed, control is returned to the "txtA1_BeforeUpdate" routine in the "frmDataEntry" module.

    Here's an important point from a programming point of view - the statement "Application.EnableEvents = False" in the "Worksheet_Change" routine actually disables any Excel response to further "events". This is essential because the updating of cells A2:A5 are ALSO "events" which would in turn re-trigger the "Worksheet_Change" routine & we would find ourselves in an endless loop of code. The statement "Application.EnableEvents = True" at the end of the "Worksheet_Change" routine re-enables Excel's response to future "events".

    It's also important to ensure that your error handling code (located after the "EnableEvents:" label) also includes an "Application.EnableEvents = True" statement, otherwise en error encountered in your code may result in Excel not responding to "events" for the remainder of your Excel session. In our case the "normal" code deliberately runs on into the error handling code in order to re-enable Excel's "event" handling, but in the majority of cases you'll find an "Exit Sub" statement immediately before the label at the start of the error handling code. Hope that's not too much to take on board!

    I hope this gives you an idea of what's happening, but please ask for any further explanation if you need it.

    All the best,

    Greg M

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1