move to specific cells automatically after data entry

skidda420

New Member
Joined
Jun 7, 2018
Messages
22
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
I'll try my best to explain what I am attempting to accomplish, we enter in serial numbers then a job number for shipping using a hand scanner. I scan the serial into a2 the cursor is set to move to the right to b2 to scan in the job number. I'm using a macro to move the cursor down to a3 after b2 has been entered and it works wonderfully. I scan into 'blocks' which is a block of 9 units (a2:a10 and b2:b10), this continues across the spreadsheet (for multiple job numbers) because that is how we stack the freight into the truck, in blocks of 9 then I skip 2 rows (manually) to continue scanning another block of 9 starting with a13:a21 and repeat the process. I do this with across all the rows up to row x skipping every 3rd row (c,f,i,l,o,r,u,x) which are ignored for spacing reasons. I'm attempting to get my cursor to automatically move past empty rows (a11, b11,a12, b12 etc) and go directly into my next block (a13) after data entry into b10. I've attempted using lock cells and protecting the worksheet but instead it moves the cursor to d10 so i am needing to add to this macro. I know it sounds a little confusing, hopefully there's a macro wiz and I know there's a way to do it, but i'm still scratching my head. Any help is much appreciated!

'Event Handler for carriage return after scanning TLAs into certain fields Rice 7/7/2019
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim cellrange As String
'Return cursor to A cell after scanning into B cell
If Target.Column = 2 Then
cellrange = "A" & Trim(Str(Target.Row + 1))
Application.Goto Reference:=ActiveSheet.Range(cellrange), Scroll:=False
End If
'Return cursor to D cell after scanning into E cell
If Target.Column = 5 Then
cellrange = "D" & Trim(Str(Target.Row + 1))
Application.Goto Reference:=ActiveSheet.Range(cellrange), Scroll:=False
End If
'Return cursor to G cell after scanning into H cell
If Target.Column = 8 Then
cellrange = "G" & Trim(Str(Target.Row + 1))
Application.Goto Reference:=ActiveSheet.Range(cellrange), Scroll:=False
End If
'Return cursor to J cell after scanning K cell
If Target.Column = 11 Then
cellrange = "J" & Trim(Str(Target.Row + 1))
Application.Goto Reference:=ActiveSheet.Range(cellrange), Scroll:=False
End If
'Return cursor to M cell after scanning into N cell
If Target.Column = 14 Then
cellrange = "M" & Trim(Str(Target.Row + 1))
Application.Goto Reference:=ActiveSheet.Range(cellrange), Scroll:=False
End If
'Return cursor to P cell after scanning into Q cell
If Target.Column = 17 Then
cellrange = "P" & Trim(Str(Target.Row + 1))
Application.Goto Reference:=ActiveSheet.Range(cellrange), Scroll:=False
End If
'Return cursor to S cell after scanning into T cell
If Target.Column = 20 Then
cellrange = "S" & Trim(Str(Target.Row + 1))
Application.Goto Reference:=ActiveSheet.Range(cellrange), Scroll:=False
End If
'Return cursor to V cell after scanning into W cell
If Target.Column = 23 Then
cellrange = "V" & Trim(Str(Target.Row + 1))
Application.Goto Reference:=ActiveSheet.Range(cellrange), Scroll:=False
End If
End Sub
 

skidda420

New Member
Joined
Jun 7, 2018
Messages
22
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
weird, it's telling me the link either does not exist or is expired, please contact the owner of this content.
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
L

Legacy 98055

Guest
You are welcome to send me a PM for another alternative to getting the file to you.
 

Forum statistics

Threads
1,143,839
Messages
5,721,093
Members
422,339
Latest member
SHIVATVM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top