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_
    Steel 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_
        Steel 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