Results 1 to 8 of 8

Thread: move to specific cells automatically after data entry
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jun 2018
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default move to specific cells automatically after data entry

    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

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,865
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: move to specific cells automatically after data entry

    I read through your post several times, but I'm not exactly understanding. In any case, this is an example that uses named ranges. Name your blocks in some sheet and save that sheet as a template of sort. This code will go from cell to cell to the last cell of the block and will then go to the first cell of the next block. I hope the link I sent works. I'll get back to you this evening.

    Have a nice day!

    Tom

    https://1drv.ms/x/s!Anxrq_k7ozqagXyQhVHgkDhFhwMe

    I tried the link. Open it, select file, SaveAs, download a copy. Please let me know if it works.

    If not, paste the code in your worksheet, name A2:B10 "Block1", name A13:B21 "Block2". The blocks can be anywhere you want.

    Code:
    Private Const numBlocks = 2
    
    
    Private Sub Worksheet_Change(ByVal Target As Range)
        MoveToRange Target
    End Sub
    
    
    Private Sub MoveToRange(Target As Range)
        Dim blockNum As Integer
        Dim Block As Range
        
        For blockNum = 1 To numBlocks
            Set Block = Range(CStr("Block" & blockNum))
            If Not Intersect(Block, Target) Is Nothing Then
                'are we in the last cell of the block?
                If Target.Address = Block(Block.Cells.Count).Address Then
                    'if so, go to the first cell in the next block
                    Range(CStr("Block" & blockNum + 1)).Cells(1).Select
                Else
                    'if not, go to the next cell in the current block
                    Block(GetIndexInNamedRange(Block, Target) + 1).Select
                End If
            End If
        Next
    End Sub
    
    
    Private Function GetIndexInNamedRange(Block As Range, Target As Range) As Integer
        Dim index As Integer
        Do
            index = index + 1
        Loop Until Block(index).Address = Target.Address
        GetIndexInNamedRange = index
    End Function
    Last edited by Tom Schreiner; Sep 13th, 2019 at 07:18 AM.

  3. #3
    New Member
    Join Date
    Jun 2018
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: move to specific cells automatically after data entry

    I was able to download it and get it working through the downloaded update, but unsure on if I should add this into my current macro or just as it is. Also not sure on what you mean by a 'template of sorts'. Thank you again.

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,865
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: move to specific cells automatically after data entry

    Hi. The code I provided should replace your existing code though it will need some beefing up before all is said and done. As far as using your worksheet as a template, because I don't understand other details about your process, I'm assuming that at some point you reuse or move on to another worksheet, filling it with new data while using the same structure. The gist of my example is that you name your blocks in the order that you wish to enter your data. Block1, Block2, etc. Navigation simply goes left to right and down until you reach the end of the current block and then moves on to the next block in the sequence. Using this method would easily allow for other block structures if you need them. If I'm missing the mark, give me some more infor.

  5. #5
    New Member
    Join Date
    Jun 2018
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: move to specific cells automatically after data entry

    Quote Originally Posted by Tom Schreiner View Post
    Hi. The code I provided should replace your existing code though it will need some beefing up before all is said and done. As far as using your worksheet as a template, because I don't understand other details about your process, I'm assuming that at some point you reuse or move on to another worksheet, filling it with new data while using the same structure. The gist of my example is that you name your blocks in the order that you wish to enter your data. Block1, Block2, etc. Navigation simply goes left to right and down until you reach the end of the current block and then moves on to the next block in the sequence. Using this method would easily allow for other block structures if you need them. If I'm missing the mark, give me some more infor.
    You've pretty much hit the mark, here is a shared version of what I've been working on, you will see that I have multiple sheets (bays) with a total of 13 blocks spanning across the entire sheet.

    https://1drv.ms/x/s!AhoAkEPkXHjag90QvG7LnrH8_22PGg

    Have at you, if I can cut down the amount of coding and achieve the same process I'll be tickled, thank you so much for your help on this.
    Last edited by skidda420; Sep 18th, 2019 at 04:00 PM.

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,865
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: move to specific cells automatically after data entry

    Hi again. I looked at your workbook and I'm pretty sure that I understand your process. However, I do have a question. Your data validation. Are you attempting to enforce a unique serial# within each worksheet or within all worksheets collectively?
    Last edited by Tom Schreiner; Sep 18th, 2019 at 08:11 PM.

  7. #7
    New Member
    Join Date
    Jun 2018
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: move to specific cells automatically after data entry

    Quote Originally Posted by Tom Schreiner View Post
    Hi again. I looked at your workbook and I'm pretty sure that I understand your process. However, I do have a question. Your data validation. Are you attempting to enforce a unique serial# within each worksheet or within all worksheets collectively?


    all worksheets, one of the biggest issues we were running into was multiple scans of the same serial number and billing the customer twice for the same product.I was just switching between bays (sheets) when I was scanning. Never got around to data validation per sheet, actually never even thought of that lol

  8. #8
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,865
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: move to specific cells automatically after data entry


Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •