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.
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
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 …
Suggested Courses
Course of the Month7 days, 8 hours left to enroll
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.