Excel VBA - range offset

Question

I'm trying to make a code which after insert/update value in one cell, will go to the next cell like on the picture. Now this code works only one way:

ActiveCell.Offset(1,0).Select ex. from A2 to B2.

What to do to return offset to the cell on the left from B2 to A3 - need loop like on the picture.

Elio Fernandes · Accepted Answer · 2021-08-31 17:49:08Z

2

I am not quite sure if I understood what you are looking! I set a range on column A and B until row 10, just for testing.

This code moves to the next cell on the picture whenever you edit the cell and then you press Enter.

Put this code on the sheet module

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngColA As Range: Set rngColA = Range("A2:A10")
    Dim rngColB As Range: Set rngColB = Range("B2:B10")
    
    If Not Intersect(Target, rngColA) Is Nothing Then
        Target.Offset(0, 1).Select
        exit sub
    End If

    If Not Intersect(Target, rngColB) Is Nothing Then
        Target.Offset(1, -1).Select
    End If
End Sub

edited Aug 31, 2021 at 17:49

answered Aug 31, 2021 at 17:44

Elio Fernandes

1,4302 gold badges16 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Artec Over a year ago

YES - that is what I need. Thanks a lot @Elio Fernandes

Collectives™ on Stack Overflow

Excel VBA - range offset

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related