Vincent88
Active Member
- Joined
- Mar 5, 2021
- Messages
- 382
- Office Version
- 2019
- Platform
- Windows
- Mobile
I want to set the cells' movement to right after entering data in a dynamic range of cells (Outside the range - move down after press enter)
but it happens outside the dynamic range also. How should I correct it ?
VBA Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Select Case Sh.Name
Case "Agents"
Exit Sub
Case Else
End Select
Dim LastColumn As Long, LastRow As Long
'Set Cursor Direction to Right in Dynamic Range Only
LastRow = sh.Range("A2").End(xlDown).Row
LastColumn = sh.Range("A2").CurrentRegion.Columns.Count
If Target.Column > 4 Or Target.CountLarge > 1 Then Exit Sub
If Target.Row = 1 Then Exit Sub
Dim rng As Range
Set rng = Range(Cells(2, 1), Cells(LastRow, LastColumn))
With rng
If Target.Column = 2 And Not (IsEmpty(Target)) Then
Target.Offset(, 2).Select
Else
Target.Offset(, 1).Select
End If
End With
End Sub