• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 270
  • Last Modified:

Data Entry Macro Excel

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
snapper1
Asked:
snapper1
  • 2
  • 2
  • 2
  • +1
1 Solution
 
Rgonzo1971Commented:
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
 
snapper1Author Commented:
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
 
Rgonzo1971Commented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Rob HensonFinance AnalystCommented:
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
 
Rob HensonFinance AnalystCommented:
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
 
regmigrantCommented:
Or you could define the range as a Table and have a formula in column a to create the next row number
0
 
snapper1Author Commented:
this looks an improvement on what I was doing
0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now