How can I enter data in a worksheet in a specific order

Ivn68

Board Regular
Joined
Aug 21, 2015
Messages
86
How can I enter data in a certain specific order for example
A1 B1 A2 B2 C1 D1 C2 D2
So when I enter data it will go to the next specified cell
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
I suggest that you update your forum profile (click your user name at the top right of the forum, then ‘Account details’) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Assuming that you are entering one value at a time, see if this is what you want. It is a vba Worksheet_Change event code and is implemented like this ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.CountLarge = 1 Then
    Select Case Target.Address(0, 0)
      Case "A1", "A2", "C1", "C2"
        Target.Offset(, 1).Select
      Case "B1", "D1"
        Target.Offset(1, -1).Select
      Case "B2"
        Target.Offset(-1, 1).Select
    End Select
  End If
End Sub
 
Upvote 0
What if I wanna make it
A1 B1 A2 B2 A3 B3 C1 D1 C2 D2 C3 D3 E1 F1

Do you see what I'm trying to do?
  1. How would we know when you wanted to move from column B to the top of column C or from column D to the top of column E?

  2. How do we know when (or if) to stop moving across another two columns?
 
Upvote 0
Perhaps it is more like this?

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  Const LastRow As Long = 4
  
    If Target.CountLarge = 1 Then
    Select Case True
      Case Target.Column Mod 2 = 1 And Target.Row <= LastRow
        Target.Offset(, 1).Select
      Case Target.Column Mod 2 = 0 And Target.Row < LastRow
        Target.Offset(1, -1).Select
      Case Target.Column Mod 2 = 0 And Target.Row = LastRow
        Target.Offset(-Target.Row + 1, 1).Select
    End Select
  End If
End Sub
 
Upvote 0
Try
Rich (BB code):
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim myList, x
    myList = Split("A1,B1,A2,B2,A3,B3,C1,D1,C2,D2,C3,D3,E1,F1", ",")  '<--- change as you like it
    x = Application.Match(Target.Address(0, 0), myList, 0)
    If IsError(x) Then Exit Sub
    If myList(x - 1) = myList(UBound(myList)) Then x = 0
    Range(myList(x)).Select
End Sub
 
Upvote 0
When you hold down control and select cells in a certain order you can then enter data in that order. How do I make that permanent
 
Upvote 0
When you hold down control and select cells in a certain order you can then enter data in that order. How do I make that permanent
This is a very different question and a duplicate to: How to make control select cell pattern permanent

In future, please do not post the same question multiple times. See Forum Rules (#12).

In relation to the post #7 question here please continue in the linked thread. Any responses posted here should be in relation the the question(s) asked in the earlier part of this thread.
 
Upvote 0

Forum statistics

Threads
1,226,269
Messages
6,189,956
Members
453,584
Latest member
daihoctuxaeptit

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