Solved

Data Entry Macro Excel

Posted on 2014-03-20
7
252 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 48

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 48

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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 31

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 31

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
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 …

757 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now