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.
Course Of The Month
9 days, 11 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Data Entry Macro Excel
Posted on 2014-03-20
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
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
[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
Active today
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
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
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
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
Active today
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
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
Active today
Or using the Worksheet Change event and Move right after enter. This also allows for undo:
This also just enters straight into the cell rather than an input box.
Thanks
Rob H
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveCell.Column = 5 Then ActiveCell.Offset(1, -4).Activate
End Sub
This also just enters straight into the cell rather than an input box.
Thanks
Rob H
Active today
If you try to select beyond column D, this will tke you back to column A in the next row:
Rob H
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveCell.Column > 4 Then Cells(ActiveCell.Row + 1, 1).Activate
End Sub
Rob H
Featured Post
Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
- Document Imaging
- Microsoft Office
- Images and Photos
- Office 365
- Office / Productivity
- Photos / Graphics Software, *Office 2013, *office 2010, *Office 2016
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month9 days, 11 hours left to enroll
623 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.