Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Data Entry Macro Excel

Posted on 2014-03-20
7
Medium Priority
?
263 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 52

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 52

Accepted Solution

by:
Rgonzo1971 earned 1500 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
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
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 …

715 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