CODEWITHSUNDEEP
excelvba
Srocchio
Srocchio

Reputation: 1

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

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

Upvotes: 0

Views: 61

Answers (1)

Tim Williams
Tim Williams

Reputation: 166316

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

Upvotes: 1

Related Questions