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.

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