Solved

Data Entry Macro Excel

Posted on 2014-03-20
7
258 Views
Last Modified: 2014-03-22
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
0
Comment
Question by:snapper1
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 39944529
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
 

Author Comment

by:snapper1
ID: 39944717
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
 
LVL 50

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 39944815
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 33

Expert Comment

by:Rob Henson
ID: 39945120
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
 
LVL 33

Expert Comment

by:Rob Henson
ID: 39945136
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
 
LVL 19

Expert Comment

by:regmigrant
ID: 39946335
Or you could define the range as a Table and have a formula in column a to create the next row number
0
 

Author Closing Comment

by:snapper1
ID: 39947269
this looks an improvement on what I was doing
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Perhaps more familiar to developers who primarily use VBScript than to developers who tend to work only with Microsoft Office and Visual Basic for Applications (VBA), the Dictionary is a powerful and versatile class, and is useful …
Outlook Free & Paid Tools
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question