• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Cell Direction Macro

Steve Morris

New Member
I need help with direction of input to move right to left then to move down following the number trend on the sample above. The goal is to scan two items then to move to the next line. I assume a macro is necessary to move in these directions.
 

Attachments

  • Cell Direction Help.xlsx
    23.5 KB · Views: 1
How is data getting input? Is a user entering data by typing, or are you actually scanning something? If we make the two columns a table, you could just hit Tab after evey cell, and it would follow the desired pattern.
 
The user will scan two bar codes from one item which would be the scan 1 and scan 2 moving left to right. My goal was to eliminate any keyboard intervention and just move through the cells after a scan is entered.
 
Does your scanner have any options to change what is does after running the scan? Currently, when a user scans something, which of these occurs?
  • Move down one cell
  • Move right one cell
  • Stay in Cell edit mode
  • Stay on current cell, but not editing
 
While we may need to go to a macro, here's an attempt at non-macro solution:
Select the two column area where you're scanning items. Hit Ctrl+l, check the box for "My table has headers". This will convert your Range into an XL table. Then, in the Excel Options - Advanced, change the direction after Enter to be 'Right'. If I test this now manually, the cursor follows the correct pattern, of moving along one row, and then dropping to next line.

Or, if you want to go straight to the macro, right-click on sheet tab, view code, paste this in.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'Check which cell was changed
If Intersect(Target, Range("B:C")) Is Nothing Then Exit Sub
'Don't do anything if multiple cells changed
If Target.Count > 1 Then Exit Sub

'Setup how cursor should move
Select Case Target.Column
    Case 2
        Target.Offset(, 1).Select
    Case 3
        Target.Offset(1, -1).Select
End Select

End Sub
 
Back
Top