How to move to next step if cell is already populated? (Automatic Cell Movement for Data Entry in Excecl (VBA))

Question

If you have an array 11 cells wide, 2 rows deep. After iterating through the following code for one row, Column 3 on the second row will already be populated.

How do I skip to column 5 on the second row, if after the end of the second row I would like to restart at column 3 (which will not be populated) for the next 2x11 array?

Using the following code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Target.Cells.CountLarge > 1 Then
    If Not Intersect(Target, Columns(3)) Is Nothing Then
        Target.Offset(, 2).Select
    ElseIf Not Intersect(Target, Columns(5)) Is Nothing Then
        Target.Offset(, 1).Select
    ElseIf Not Intersect(Target, Columns(6)) Is Nothing Then
        Target.Offset(, 2).Select
    ElseIf Not Intersect(Target, Columns(8)) Is Nothing Then
        Target.Offset(, 2).Select
    ElseIf Not Intersect(Target, Columns(10)) Is Nothing Then
        Target.Offset(, 1).Select
    ElseIf Not Intersect(Target, Columns(11)) Is Nothing Then
        Target.Offset(, -2).Select
    ElseIf Not Intersect(Target, Columns(9)) Is Nothing Then
        Target.Offset(1, -6).Select
    End If
End If

What's the ultimate goal here? It's generally best practice to [avoid using `.Select`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — BruceWayne, Feb 19 '19 at 17:33
@BruceWayne - in general Yes but difficult to avoid in this specific use case ? — Tim Williams, Feb 19 '19 at 19:00
@TimWilliams - Maybe yeah. If the idea is to simply, literally, select the cell so it's active for the User, then yeah obviously this would be okay. I'm just wondering if the next step is something more "concrete"? — BruceWayne, Feb 19 '19 at 19:08

score 1 · Answer 1 · answered Feb 19 '19 at 17:47

You can do something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rw As Range

    If Not Target.Cells.CountLarge > 1 Then

        Set rw = Target.EntireRow

        Select Case Target.Column
            Case 3: rw.Cells(5).Select
            Case 5: rw.Cells(6).Select
            Case 6: rw.Cells(8).Select
            Case 8: rw.Cells(10).Select
            Case 10: rw.Cells(11).Select
            Case 11: rw.Cells(9).Select
            Case 9:
                'not exactly following your layout, so you may need to 
                '   adjust this test to fit your needs
                If rw.Row Mod 2 = 0 Then
                    rw.Cells(3).Offset(1, 0).Select
                Else
                    rw.Cells(5).Offset(1, 0).Select
                End If
        End Select
    End If

End Sub

It will check whether a row is odd/even to determine where to start in the next entry line

Thank you, this solution was exactly what I needed. Any recommendations for how I would execute this with a 3 row arrays (instead of two)? — Srocchio, Feb 20 '19 at 17:02

How to move to next step if cell is already populated? (Automatic Cell Movement for Data Entry in Excecl (VBA))

1 Answers1