Data Entry Macro Excel

I am looking to create what I think is a simple data entry macro for the following situation..Its obviously more complex than I can work out Though

      A      B      C      D
1      1      67      34      24
2      12      62      37      21
3      23      57      40      18
4      34      52      43      15
5      45      47      46      12
6      56      42      49      9
7      67      37      52      6
8      78      32      55      3


The first column is a record ie Record 1,2,3 4 etc
I want to enter the data into record 1 to start with so I enter data at A1 and when I enter the cell automatically moves to B1 then when I enter a number there  it moves to C1 then D1

When I enter data into D1 it moves to cell A2 so I can repeat the process for that record and so on

essentially after I have entered data into a cell it moves to the right and needs to pause whilst I have time to select the next data to enter

Thank You in Advance Everybody
snapper1Asked:
Who is Participating?
 
Rgonzo1971Commented:
HI,

let's try to select "A1" to "B2" the  3 cells are blue and one the active cell is in white enter your data and Enter it goes to A2 if Direction down then goes to  next time it goes to B1 and then to B2

but if you want a macro try ( the activecell must be in col A to D

Sub Macro()
Answer = InputBox("number")
Do While Answer <> ""
    ActiveCell = Answer
    If ActiveCell.Column = 4 Then
        ActiveCell.Offset(1, -3).Activate
    Else
        ActiveCell.Offset(0, 1).Activate
    End If
    Answer = InputBox("number")
Loop
End Sub

Open in new window

Regards
0
 
Rgonzo1971Commented:
HI,

You could do it by selecting Col A to D (Highlighted) and by changinging the entry direction

in File / Options / Advanced / Afterpressing Enter, Move selection and choose Right

Regards
0
 
snapper1Author Commented:
That would work but didn't address of how you get to the first column in the next row.

Since posting this I realised by watching some videos on You tube that a combination of
Input Box
ActiveCell.Value
Offset command ..which can move the next entry left, right, up down etc
Looping

will do the job.

for instance a macro like the one below would do the jobs.
Thanks for your thoughts.I had never realised you could what you suggested.

Dim i As Integer
 Do While (i < 10)
 i = (i + 1)
Answer = InputBox("number")
ActiveCell.Value = Answer
ActiveCell.Offset(0, 1).Select
Answer = InputBox("number")
ActiveCell.Value = Answer
ActiveCell.Offset(0, 1).Select
Answer = InputBox("number")
ActiveCell.Value = Answer
ActiveCell.Offset(0, 1).Select
Answer = InputBox("number")
ActiveCell.Value = Answer
ActiveCell.Offset(1, -3).Select


 Loop
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Rob HensonFinance AnalystCommented:
Or using the Worksheet Change event and Move right after enter. This also allows for undo:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If ActiveCell.Column = 5 Then ActiveCell.Offset(1, -4).Activate

End Sub

Open in new window


This also just enters straight into the cell rather than an input box.

Thanks
Rob H
0
 
Rob HensonFinance AnalystCommented:
If you try to select beyond column D, this will tke you back to column A in the next row:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If ActiveCell.Column > 4 Then Cells(ActiveCell.Row + 1, 1).Activate

End Sub

Open in new window

Thanks
Rob H
0
 
regmigrantCommented:
Or you could define the range as a Table and have a formula in column a to create the next row number
0
 
snapper1Author Commented:
this looks an improvement on what I was doing
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.