How to Make Excel Move Automatically to the Next Cell: 4 Suitable Methods

Method 1 – Using VBA to Make Excel Move Automatically to Next Cell

Steps:

Go to the Developer tab >> click on Visual Basic.

The Microsoft Visual Basic for Applications will open.
Go to the Insert tab >> Select Module.

Write the following code in your Module.

Sub Move_to_Next_Cell()
Dim Str As String
Dim start_position As Integer
Dim column_number As Integer
Str = Application.InputBox("Enter value", "Move to Next Cell", , , , , , 2)
column_number = 0
Application.ScreenUpdating = False
For start_position = 1 To Len(Str) Step 5
    ActiveCell.Offset(0, column_number) = "'" & Mid(Str, start_position, 5)
    column_number = column_number + 1
Next
Application.ScreenUpdating = True
End Sub

Code Breakdown

We created a Sub Procedure as Move_to_Next_Cell.
Declared Str as String, start_position, and column_number as Integer.
Set InputBox named as Move to Next Cell to Enter Value.
We selected column_number=0
We set the Screen Updating to False.
Used a For loop from start_position = 1 to the length of the Str and set Step as 5.
Used the VBA Mid function to extract the first 5 character and by using the VBA Offset function moved the extracted values to the next cell.
Incremented the column_Number by using column_Number = column_Number+ 1.
Set the Screen Updating as True.

Click on the Save button and go back to your worksheet.

Select the Cell C5.
Go to the Developer tab >> click Macros.

The Macros box will appear.
Select Move_to_Next_Cell macro.
Click Run.

The Move to Next Cell box will open.
In the Enter value box, select Cell B5.
Click OK.

See that the Cell value of B5 has moved to Cell C5 and Cell D5. Cells C5 contains the first 5 letters and Cell D5 contains the rest of the 5 letters.

Follow the same steps we took for Cell D5 for the rest of the cells to move Excel automatically to the next cell.

Method 2 – Use of Advanced Feature from Excel Options to Move Automatically to Next Cell

Steps:

Select and click on Cell B5.
Go to the File tab.

Click Options.

The Excel Options box will open.
Go to the Advanced option.
Turn on the After pressing Enter, move selection and set Direction as Down.
Press OK.

Click ENTER.
See that Cell B5 has moved downward to Cell B6.

Method 3 – Applying Protect Sheet Feature to Move Automatically to Next Cell

Apply the Protect Sheet Feature to move Excel automatically to the next cell.

Go through the steps given below to do it on your own.

Steps:

Select the Cell range B4:D10 and right-click.

Click on Format Cells.

The Format Cells box will appear.
Go to the Protection tab.
Unselect the Locked option.
Click on OK.

Go to the Review tab >> click on Protect >> select Protect Sheet.

The Protect Sheet box will open.
Turn on the Protect worksheet and contents of locked cells.
Set a Password. Here, we set “123” as a Password.
Unselect the Select locked cells option.
Click OK.

To confirm the Password, type the Password again in the box.
Click OK.

Click on Cell B10.

Press ENTER.
See that Cell B10 has moved to Cell C4 rather than Cell B11 as the Cell range has been locked using the Protect Sheet Feature.

Method 4 – Use of Name Box to Make Excel Move Automatically to Next Specific Cell

Steps:

Select Cell F6.
Press CTRL and select Cell B9, C9, D9, E9, F9, and C6.

Click on the Name box.
Type Marks.
Press ENTER.

See that Cell C6 is selected.
Insert any Name of your own preference. Insert “Lucas”.

Press ENTER.
Cell C6 will move to Cell F6.
Insert “12001” as Roll.

Press ENTER the Cells will move to Cell B9, C9, D9, E9, and F9 and insert the data according to your preference.

We inserted 94, 81, 77, 65, and 85 in Cell B9, C9, D9, E9, and F9.

Download Practice Workbook

Automatically Move to Next Cell.xlsm

<< Go Back to Excel Cells | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

Private Sub Worksheet_Change(ByVal Target As Range) Dim activeRowChange_column As String activeRowChange_column = "C" ' change this according to your requirement Set activeRowChange_range = ThisWorkbook.ActiveSheet. _ Range(activeRowChange_column & ":" & activeRowChange_column) If Not Intersect(Target, activeRowChange_range) Is Nothing Then Target.Offset(1, -2).Select End If End Sub

2 Comments

Ole Dagfinn Tandberg Oct 12, 2023 at 5:47 PM

I have two Excel sheets:

Startlist: With bib number and starttime and names

Results: I using VBA to lookup bib numbers from startlist and then calculate finish time

Problem: RFID reader put in bib number but i need to push enter key so Excel is ready for next value from RDID reader. I want Excel to automaticly move to next cell under after RFID value is set by RFIS reader

Regards
Ole Dagfinn Tandberg
- Reply
  Seemanto Saha Oct 16, 2023 at 10:48 AM
  Hello OLE DAGFINN TANDBERG,
  Thanks for sharing your problem with us. I understand that you are facing problems with automatic value entry from the RFID reader.
  
  Usually, an RFID reader places values (e.g. bib number, name, start time, finish time, etc.) in cells of newer rows automatically by moving one row down. That means if the first RFID reading places values in Row 1, then the second RFID reading should automatically place values in ROW 2, the third RFID reading should automatically place values in ROW 3, and so on.
  
  But in your case, you have to press the Enter key to move one row down. This is probably due to the RFID reader or software configuration. It is possible that the RFID reader software places values in cells of Active Row (i.e. the row of Active Cell) but does not offset the active cell by 1 row down for the next set of entries.
  
  The best solution to this problem is to modify the settings of the RFID reader. If that is not possible, you can use a VBA code to change the Active Row each time an RFID reading is performed.
  
  Right-click over the Sheet Tab of the Startlist sheet and select the View Code option.
  
  At this point, the Visual Basic Editor for that sheet will open. Insert the following code in the editor module.
  
  Excel VBA Code
```
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim activeRowChange_column As String
    activeRowChange_column = "C" ' change this according to your requirement
    
    Set activeRowChange_range = ThisWorkbook.ActiveSheet. _
    Range(activeRowChange_column & ":" & activeRowChange_column)
    
    If Not Intersect(Target, activeRowChange_range) Is Nothing Then
        Target.Offset(1, -2).Select
    End If
End Sub
```
  As your Startlist sheet would have 3 values (i.e. bib number, start time, and name), I have assumed that the cell from the Active Row of Column C will be the last cell updated from an RFID reading. When a cell in Column C is updated from the RFID reading, the active row will automatically move to the next row and be ready for the next RFID reading value entry.
  
  Repeat the same steps for the “Results” sheet as well.
  
  To demonstrate this actually works, we will use a User Form to enter values in the Startlist and Results sheets.
  
  After entering the Bib Number and Name, when we click the Submit button (similar to scanning a card in the RFID reader), the Bib Number, Starting Time, and Name will be saved in the Active Row of the Startlist sheet. As soon as these records are saved, the active row will automatically move to the row below. You can notice this in the following GIF:
  
  Now, If you enter another Bib Number and name, and click the Submit button it will be saved in a new row. You can watch this in the following GIF:
  
  On the other hand, if you reenter the Bib Number and Name, and click the Submit button, it will look up the Bib Number in the Startlist sheet, calculate Final Time, and insert the Bib Number, Final Time, and Required Time in the Results sheet. As soon as these values are saved, the active row will move to the row below automatically. You can watch that in the following GIF:
  
  As the User Form was used only to demonstrate how the Active Row moves one row below, we haven’t included the code used in the User Form. But you can find the codes in the following workbook.
  
  WORKBOOK
  
  Hopefully, this solution will be helpful for you. However, as we have assumed a lot of properties of your Workbook and the RFID reader, this solution can vary from the actual required solution. Please share your workbook and the working process of the RFID reader in such an instance.
  
  Regards,
  Seemanto Saha
  ExcelDemy

How to Make Excel Move Automatically to the Next Cell: 4 Suitable Methods

Method 1 – Using VBA to Make Excel Move Automatically to Next Cell

Method 2 – Use of Advanced Feature from Excel Options to Move Automatically to Next Cell

Method 3 – Applying Protect Sheet Feature to Move Automatically to Next Cell

Method 4 – Use of Name Box to Make Excel Move Automatically to Next Specific Cell

Related Articles

Leave a reply Cancel reply