FOLLOW PATTERN FOR DATA ENTRY

S

samsilverman

Board Regular
Joined
Nov 9, 2009
Messages
176
I have a workbook with several worksheets. Each worksheet has up to 100 pages. Some information on these pages is already prefilled for the client.

There are then several cells in which new data needs to be put into every 3 months or so and I'm wondering if a pattern can be established through VBA coding or otherwise so that when it comes time to enter this new data, I hit enter it will simply go to the next cell needing input, instead of me having to move to the next cell with my mouse or laptop mouse pad.

If possible, I would want this pattern to be followed.

C16-30, then E16-30, then L14, L16, L17, then L23-27, then B39

The pattern would then pick up 47 rows down for each subsequent page and follow the same exact sequence. The next page being as follows...

C63-C77, then E63-E77, then L61, L63, L64, then L70-L74, then B86

This pattern would continue by hitting the enter key. If I want to break the pattern, I want to simply use my mouse and click on a cell to enter data there. If it is a cell within the pattern, clicking enter would then go to the next cell in the pattern.

I need it to follow this pattern, jumping from one page to the next for as many pages as I might have for a client.

The starting point for every page's pattern would be C16, C63, C110, C157, C204....and so on.

I have other worksheets that I would like to employ this on as well. Those worksheets would have a slightly different pattern and starting cells. But I'm confident once I see the initial VBA coding that I can edit the code to fit that worksheet.

Appreciate any help!!
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Sort by date Sort by votes
See if this works for you.

Put this code in the input sheet's module:
VBA Code: 
Option Explicit

Private Sub Worksheet_Change(ByVal target As Range)
    
    If target.Row >= 14 And target.Cells.Count = 1 Then
        '1st arg - current cell
        '2nd arg - input cells
        '3rd arg - number of rows that the current input cell is offset from row 16 (row number of first input cell in string): 0, 47, 94, etc.
        '4th arg - number of rows that the pattern repeats from row 16 (row number of first input cell in string): 47
        SelectNextCell target, "C16:C30,E16:E30,L14,L16,L17,L23:L27,B39", (target.Row \ 47) * 47, 47
    End If
    
End Sub
You would repeat the above code in the other input sheet modules with the appropriate arguments.

Put this code in a standard module:
VBA Code: 
Option Explicit


Public Sub SelectNextCell(currentCell As Range, inputCells As String, inputRowOffset As Long, patternRepeatRows As Long)

    Dim inputCellsCsv As String
    Dim p1 As Long, p2 As Long
    Dim nextCell As String

    'Create csv string of individual input cells
    
    inputCellsCsv = ExpandInputCells(inputCells, inputRowOffset)
    
    'Does the current cell occur in the input cells?
    
    p1 = InStr(inputCellsCsv, "," & currentCell.Address(False, False) & ",")
    If p1 > 0 Then
        'Yes - find next cell in input cells
        p1 = p1 + Len("," & currentCell.Address(False, False) & ",")
        If p1 < Len(inputCellsCsv) Then
            'Select next cell in input cells
            p2 = InStr(p1, inputCellsCsv, ",")
            nextCell = Mid(inputCellsCsv, p1, p2 - p1)
            Range(nextCell).Select
        Else
            'Reached last cell in input cells.  Select first cell in input cells offset by the number of pattern repeat rows
            p1 = InStr(inputCellsCsv, ",")
            p2 = InStr(p1 + 1, inputCellsCsv, ",")
            nextCell = Mid(inputCellsCsv, p1 + 1, p2 - p1 - 1)
            Range(nextCell).Offset(patternRepeatRows).Select
        End If
    End If

End Sub


Private Function ExpandInputCells(inputCells As String, inputRowOffset As Long) As String

    Dim allCells As String
    Dim csvRanges As Variant, csvRange As Variant
    Dim cell As Range
    
    'Create comma-separated string of all individual cells in inputCells.
    'E.g. inputCells string "C16:C30,E16:E30,L14,L16,L17,L23:L27,B39" is returned as "C16,C17,C18,C19,...,C30,E16,E17,...,E30,..." etc.
    
    allCells = ","
    csvRanges = Split(inputCells, ",")    
    For Each csvRange In csvRanges
        If InStr(csvRange, ":") Then
            For Each cell In Range(csvRange)
                allCells = allCells & cell.Offset(inputRowOffset).Address(False, False) & ","
            Next
        Else
            allCells = allCells & Range(csvRange).Offset(inputRowOffset).Address(False, False) & ","
        End If
    Next
    
    ExpandInputCells = allCells
    
End Function
 
Upvote 0
samsilverman said:
If possible, I would want this pattern to be followed.

C16-30, then E16-30, then L14, L16, L17, then L23-27, then B39

The pattern would then pick up 47 rows down for each subsequent page and follow the same exact sequence. The next page being as follows...

C63-C77, then E63-E77, then L61, L63, L64, then L70-L74, then B86
Click to expand...

Here's another way:
Example on how it works:
When you finish updating cell C30 then hit Enter the code will activate cell E16. And it also work on every 47 rows below it.
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
'This is an Event Procedure, you need to put it in the code module of the sheet in question (say sheet1).

On Error GoTo skip:

If Target.Cells.CountLarge > 1 Then Exit Sub
    
    If InStr("|3|5|12|", "|" & Target.Column & "|") Then
    Dim f As Range
        Set f = Cells(Target.Row Mod 47, Target.Column)
        
        Application.EnableEvents = False
        'C16-30, then E16-30, then L14, L16, L17, then L23-27, then B39
            Select Case f.Address(0, 0)
                Case "C30"
                    Target.Offset(-14, 2).Activate
                Case "E30"
                    Target.Offset(-16, 7).Activate
                Case "L17"
                    Target.Offset(6, 0).Activate
                Case "L27"
                    Target.Offset(12, -10).Activate
            End Select
            
        Application.EnableEvents = True
    End If

Exit Sub
skip:
Application.EnableEvents = True
MsgBox "Error number " & Err.Number & " : " & Err.Description
End Sub
 
Upvote 0
John_w said:
I'm afraid your code doesn't work for me, as far as I understand the question.

Two examples: Enter value in L14 and it moves to L15, not L16. Enter value in B39 and it moves to B40, not C63.
Click to expand...
Ah, you're right I forgot about after updating B39 it should go to C63.
As for L14, I chose not to include it as it only needs 2 Enter to go to L16, but it can be easily added if needed.
Thanks for reviewing my code. (y)

Here's the revised code:
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
'This is an Event Procedure, you need to put it in the code module of the sheet in question (say sheet1).

On Error GoTo skip:

If Target.Cells.CountLarge > 1 Then Exit Sub
    
    If InStr("|2|3|5|12|", "|" & Target.Column & "|") Then
    Dim f As Range
        Set f = Cells(Target.Row Mod 47, Target.Column)
        
        Application.EnableEvents = False
        'C16-30, then E16-30, then L14, L16, L17, then L23-27, then B39 to C63
            Select Case f.Address(0, 0)
                Case "C30"
                    Target.Offset(-14, 2).Activate
                Case "E30"
                    Target.Offset(-16, 7).Activate
                Case "L17"
                    Target.Offset(6, 0).Activate
                Case "L27"
                    Target.Offset(12, -10).Activate
                Case "B39"
                    Target.Offset(24, 1).Activate
    
            End Select
            
        Application.EnableEvents = True
    End If

Exit Sub
skip:
Application.EnableEvents = True
MsgBox "Error number " & Err.Number & " : " & Err.Description
End Sub
 
Upvote 0
John_w said:
I'm afraid your code doesn't work for me, as far as I understand the question.

Two examples: Enter value in L14 and it moves to L15, not L16. Enter value in B39 and it moves to B40, not C63.
Click to expand...

John_w said:
See if this works for you.

Put this code in the input sheet's module:
VBA Code: 
Option Explicit

Private Sub Worksheet_Change(ByVal target As Range)
   
    If target.Row >= 14 And target.Cells.Count = 1 Then
        '1st arg - current cell
        '2nd arg - input cells
        '3rd arg - number of rows that the current input cell is offset from row 16 (row number of first input cell in string): 0, 47, 94, etc.
        '4th arg - number of rows that the pattern repeats from row 16 (row number of first input cell in string): 47
        SelectNextCell target, "C16:C30,E16:E30,L14,L16,L17,L23:L27,B39", (target.Row \ 47) * 47, 47
    End If
   
End Sub
You would repeat the above code in the other input sheet modules with the appropriate arguments.

Put this code in a standard module:
VBA Code: 
Option Explicit


Public Sub SelectNextCell(currentCell As Range, inputCells As String, inputRowOffset As Long, patternRepeatRows As Long)

    Dim inputCellsCsv As String
    Dim p1 As Long, p2 As Long
    Dim nextCell As String

    'Create csv string of individual input cells
   
    inputCellsCsv = ExpandInputCells(inputCells, inputRowOffset)
   
    'Does the current cell occur in the input cells?
   
    p1 = InStr(inputCellsCsv, "," & currentCell.Address(False, False) & ",")
    If p1 > 0 Then
        'Yes - find next cell in input cells
        p1 = p1 + Len("," & currentCell.Address(False, False) & ",")
        If p1 < Len(inputCellsCsv) Then
            'Select next cell in input cells
            p2 = InStr(p1, inputCellsCsv, ",")
            nextCell = Mid(inputCellsCsv, p1, p2 - p1)
            Range(nextCell).Select
        Else
            'Reached last cell in input cells.  Select first cell in input cells offset by the number of pattern repeat rows
            p1 = InStr(inputCellsCsv, ",")
            p2 = InStr(p1 + 1, inputCellsCsv, ",")
            nextCell = Mid(inputCellsCsv, p1 + 1, p2 - p1 - 1)
            Range(nextCell).Offset(patternRepeatRows).Select
        End If
    End If

End Sub


Private Function ExpandInputCells(inputCells As String, inputRowOffset As Long) As String

    Dim allCells As String
    Dim csvRanges As Variant, csvRange As Variant
    Dim cell As Range
   
    'Create comma-separated string of all individual cells in inputCells.
    'E.g. inputCells string "C16:C30,E16:E30,L14,L16,L17,L23:L27,B39" is returned as "C16,C17,C18,C19,...,C30,E16,E17,...,E30,..." etc.
   
    allCells = ","
    csvRanges = Split(inputCells, ",")   
    For Each csvRange In csvRanges
        If InStr(csvRange, ":") Then
            For Each cell In Range(csvRange)
                allCells = allCells & cell.Offset(inputRowOffset).Address(False, False) & ","
            Next
        Else
            allCells = allCells & Range(csvRange).Offset(inputRowOffset).Address(False, False) & ","
        End If
    Next
   
    ExpandInputCells = allCells
   
End Function
Click to expand...
Thank you both so much, but...

I couldn't get either to work. I think perhaps I am doing something wrong or not enough.

John W - I put the first code into the VENTILATION sheet's module and then the second code you provided into Module 2, which I created in the Modules folder. When I went back to the workbook to test it, I immediately got an error msg that read "Compile Error: Ambiguous Name Detected: Worksheet_Change" It then went back to the worksheet module and highlighted the first row of code...Private Sub Worksheet_Change(ByVal target As Range.

Was I supposed to rename something in that line to fit my particular sheet? Also, is there any other editing I need to do to the code to work in my sheets? I tried switching out "Private Sub Worksheet_Change" for "Private Sub VENTILATION_Change" and the error msg DID go away. But it didn't seem to work. Starting in cell C16 and hitting the enter key just kept taking me straight down the page.

Akuini - Is your code just a stand-alone code that goes in a general module or worksheet module? Or is it designed to work with some part of John W's code?
Was I supposed to rename something in that line to fit my particular sheet? Also, is there any other editing I need to do to the code to work in my sheets?

Sorry guys, but I'm a real novice who only knows enough to be dangerous.
 
Upvote 0
Akuini said:
Ah, you're right I forgot about after updating B39 it should go to C63.
As for L14, I chose not to include it as it only needs 2 Enter to go to L16, but it can be easily added if needed.
Thanks for reviewing my code. (y)

Here's the revised code:
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
'This is an Event Procedure, you need to put it in the code module of the sheet in question (say sheet1).

On Error GoTo skip:

If Target.Cells.CountLarge > 1 Then Exit Sub
   
    If InStr("|2|3|5|12|", "|" & Target.Column & "|") Then
    Dim f As Range
        Set f = Cells(Target.Row Mod 47, Target.Column)
       
        Application.EnableEvents = False
        'C16-30, then E16-30, then L14, L16, L17, then L23-27, then B39 to C63
            Select Case f.Address(0, 0)
                Case "C30"
                    Target.Offset(-14, 2).Activate
                Case "E30"
                    Target.Offset(-16, 7).Activate
                Case "L17"
                    Target.Offset(6, 0).Activate
                Case "L27"
                    Target.Offset(12, -10).Activate
                Case "B39"
                    Target.Offset(24, 1).Activate
   
            End Select
           
        Application.EnableEvents = True
    End If

Exit Sub
skip:
Application.EnableEvents = True
MsgBox "Error number " & Err.Number & " : " & Err.Description
End Sub
Click to expand...
Thank you both so much, but...

I couldn't get either to work. I think perhaps I am doing something wrong or not enough.

John W - I put the first code into the VENTILATION sheet's module and then the second code you provided into Module 2, which I created in the Modules folder. When I went back to the workbook to test it, I immediately got an error msg that read "Compile Error: Ambiguous Name Detected: Worksheet_Change" It then went back to the worksheet module and highlighted the first row of code...Private Sub Worksheet_Change(ByVal target As Range.

Was I supposed to rename something in that line to fit my particular sheet? Also, is there any other editing I need to do to the code to work in my sheets? I tried switching out "Private Sub Worksheet_Change" for "Private Sub VENTILATION_Change" and the error msg DID go away. But it didn't seem to work. Starting in cell C16 and hitting the enter key just kept taking me straight down the page.

Akuini - Is your code just a stand-alone code that goes in a general module or worksheet module? Or is it designed to work with some part of John W's code?
Was I supposed to rename something in that line to fit my particular sheet? Also, is there any other editing I need to do to the code to work in my sheets?

Sorry guys, but I'm a real novice who only knows enough to be dangerous.
 
Upvote 0
samsilverman said:
Akuini - Is your code just a stand-alone code that goes in a general module or worksheet module? Or is it designed to work with some part of John W's code?
Was I supposed to rename something in that line to fit my particular sheet? Also, is there any other editing I need to do to the code to work in my sheets?
Click to expand...
You don't need to change anything. It goes in worksheet module, as I commented in the code:
'This is an Event Procedure, you need to put it in the code module of the sheet in question (say sheet1).

And this is a revised version to make it faster:
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
'This is an Event Procedure, you need to put it in the code module of the sheet in question (say sheet1).

On Error GoTo skip:

If Target.Cells.CountLarge > 1 Then Exit Sub
   
    If InStr("|2|3|5|12|", "|" & Target.Column & "|") Then 'relevant columns
           
            If InStr("|30|17|27|39|", "|" & Target.Row Mod 47 & "|") Then 'relevant rows
                Application.EnableEvents = False
                    'C16-30, then E16-30, then L14, L16, L17, then L23-27, then B39 to C63
                    Select Case Target.Column & "," & Target.Row Mod 47
                        Case "3,30"  'C30
                            Target.Offset(-14, 2).Activate
                        Case "5,30" 'E30
                            Target.Offset(-16, 7).Activate
                        Case "12,17"  'L17
                            Target.Offset(6, 0).Activate
                        Case "12,27" 'L27
                            Target.Offset(12, -10).Activate
                        Case "2,39"  'B39
                            Target.Offset(24, 1).Activate
                    End Select
                Application.EnableEvents = True
            End If
           
    End If

Exit Sub
skip:
Application.EnableEvents = True
MsgBox "Error number " & Err.Number & " : " & Err.Description
End Sub

Try insert something in C30, the focus will go to E16.

Example:
www.mediafire.com

samsilverman

MediaFire is a simple to use free service that lets you put all your photos, documents, music, and video in a single place so you can access them anywhere and share them everywhere.
www.mediafire.com
 
Upvote 0
samsilverman said:
John W - I put the first code into the VENTILATION sheet's module and then the second code you provided into Module 2, which I created in the Modules folder. When I went back to the workbook to test it, I immediately got an error msg that read "Compile Error: Ambiguous Name Detected: Worksheet_Change" It then went back to the worksheet module and highlighted the first row of code...Private Sub Worksheet_Change(ByVal target As Range.

Was I supposed to rename something in that line to fit my particular sheet? Also, is there any other editing I need to do to the code to work in my sheets? I tried switching out "Private Sub Worksheet_Change" for "Private Sub VENTILATION_Change" and the error msg DID go away. But it didn't seem to work. Starting in cell C16 and hitting the enter key just kept taking me straight down the page.
Click to expand...
You have put the two pieces of code in the correct modules, if indeed the VENTILATION sheet is your data input sheet. You don't need to change any of my code.

The Compile Error: Ambiguous Name Detected: Worksheet_Change suggests there are two Private Sub Worksheet_Change(ByVal Target As Range) routines in the VENTILATION sheet's module. You can only have one such routine in the sheet module and if you are already using the Worksheet_Change event handler then my code will need to be integrated into the existing code.

samsilverman said:
Akuini - Is your code just a stand-alone code that goes in a general module or worksheet module? Or is it designed to work with some part of John W's code?
Was I supposed to rename something in that line to fit my particular sheet? Also, is there any other editing I need to do to the code to work in my sheets?
Click to expand...
Akuini's code is a different solution and completely separate from mine. You should test both solutions in separate workbooks.
 
Upvote 0
John_w said:
You have put the two pieces of code in the correct modules, if indeed the VENTILATION sheet is your data input sheet. You don't need to change any of my code.

The Compile Error: Ambiguous Name Detected: Worksheet_Change suggests there are two Private Sub Worksheet_Change(ByVal Target As Range) routines in the VENTILATION sheet's module. You can only have one such routine in the sheet module and if you are already using the Worksheet_Change event handler then my code will need to be integrated into the existing code.


Akuini's code is a different solution and completely separate from mine. You should test both solutions in separate workbooks.
Click to expand...
Thank you. Will be attempting today.
 
Upvote 0
You must log in or register to reply here.

Similar threads

franklin_m
  • Question Question
VBA Search row for instances of pattern in two adjacent cells
Replies
3
Views
430
franklin_m
franklin_m
T
  • Solved
Move cursor to a specific cell after data has been entered
Replies
4
Views
216
Peter_SSs
Peter_SSs
G
  • Solved
Format & Protect range of cells using VBA
Replies
7
Views
843
Gajendran Yadhav
G
D
  • Question Question
VBA Control Of Page Breaks
Replies
2
Views
2K
DannyDont
D
A
  • Question Question
How do I lock a worksheet and stop data entry after a certain date and time?
Replies
1
Views
514
Jeffrey Mahoney
Jeffrey Mahoney

Forum statistics

Threads
1,223,912
Messages
6,175,340
Members
452,638
Latest member
Oluwabukunmi

Share this page

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
Back
Top