Forum Discussion

Juan Pablo Gallardo's avatar
Juan Pablo Gallardo
Brass Contributor
Dec 15, 2023
Solved

Move from cell automatically

I have an excel where I scan a code on cell A1 and I need to scan another code on cell B1 and then go to cell A2 to continue with the next scan, is there a macro I can use?
excel
  • Rodrigo_'s avatar
    Rodrigo_
    Dec 17, 2023

    Juan Pablo Gallardo 
    ohh.., 

    The issue is due to the condition Target.Row Mod 2 = 1 in the macro, which only allows the macro to work on odd-numbered rows.

    Here’s the corrected macro:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 Then
        ' If a cell in column A is changed, select the cell in column B of the same row
        Cells(Target.Row, 2).Select
    ElseIf Target.Column = 2 Then
        ' If a cell in column B is changed, select the cell in column A of the next row
        Cells(Target.Row + 1, 1).Select
    End If
End Sub

    This macro will move the selection to cell B1 after a value is entered in cell A1, then to cell A2 after a value is entered in cell B1, and so on, for all rows.

Rodrigo_'s avatar
Rodrigo_
Iron Contributor
Dec 15, 2023

Juan Pablo Gallardo 
based on your explanation here's a simple example of how it could be done:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 And Target.Row Mod 2 = 1 Then
        ' If a cell in column A and an odd row number is changed, select the cell in column B of the same row
        Cells(Target.Row, 2).Select
    ElseIf Target.Column = 2 And Target.Row Mod 2 = 1 Then
        ' If a cell in column B and an odd row number is changed, select the cell in column A of the next row
        Cells(Target.Row + 1, 1).Select
    End If
End Sub

Now, every time they enter a value in cell A1, the selection will automatically move to cell B1, and then to cell A2, and so on.
Let me know if you have any other questions. 

Juan Pablo Gallardo's avatar
Juan Pablo Gallardo
Brass Contributor
Dec 16, 2023
Thank you, it works for the first entry but not the second
Enter data en A1 (successfully moves to B1) enter data on B1 (successfully moves to A2) enter data on A2 it does not move to B2
  • Rodrigo_'s avatar
    Rodrigo_
    Iron Contributor
    Dec 17, 2023

    Juan Pablo Gallardo 
    ohh.., 

    The issue is due to the condition Target.Row Mod 2 = 1 in the macro, which only allows the macro to work on odd-numbered rows.

    Here’s the corrected macro:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 Then
        ' If a cell in column A is changed, select the cell in column B of the same row
        Cells(Target.Row, 2).Select
    ElseIf Target.Column = 2 Then
        ' If a cell in column B is changed, select the cell in column A of the next row
        Cells(Target.Row + 1, 1).Select
    End If
End Sub

    This macro will move the selection to cell B1 after a value is entered in cell A1, then to cell A2 after a value is entered in cell B1, and so on, for all rows.

Resources