Feb 26 2021 06:47 AM
Feb 26 2021 06:47 AM
I have a spreadsheet where I would like to move to a specific column, based on the data entered. For example:
In A1, I enter a name: Joe
If Joe is entered, I want to go directly to column J in the same row, (without having to either tab through the columns, or use my mouse to get me to column J). and enter more data.
If I enter a different name in A1: Mary
If Mary is entered, I want to go directly to column H in the same row and enter more data.
I want to be able to do this in every row, e.g., I enter Joe in A2, then go directly to column J2, etc.
The data entered in column A is a specific list of names.
Feb 26 2021 09:29 AM
You will need a macro for that. If you right click on your worksheet and copy/paste this code into the code window that appears, then I believe it should work.
You will need to add the additional names to the code (the "case" statement). But, you should be able to just copy/paste one of the other entries, then change the name and the destination column.
Private Sub Worksheet_Change(ByVal Target As Range) Dim destCol As Range On Error GoTo ErrHandler If Intersect(Target.Cells(1), Me.Range("A:A")) Is Nothing Then Exit Sub End If Select Case LCase(Target.Cells(1).Value) Case vbNullString: Exit Sub Case "joe": Set destCol = Me.Range("J:J") Case "mary": Set destCol = Me.Range("H:H") '// Add additional names here. Here, the names should '// be in lowercase, but the user can key them in any case. Case Else: Err.Raise Number:=vbObjectError + 520, Description:="Name not recognized." End Select If Not destCol Is Nothing Then Intersect(Target.Cells(1).EntireRow, destCol).Select End If ExitProc: Exit Sub ErrHandler: MsgBox "Error " & Err.Number & ": " & Err.Description Resume ExitProc End Sub
Feb 26 2021 09:47 AM
If, by chance, you want it to return to column A after keying the data in another column, then you could change this part:
If Intersect(Target.Cells(1), Me.Range("A:A")) Is Nothing Then Exit Sub End If
With Target If Intersect(.Cells(1), Me.Range("A:A")) Is Nothing Then With .Cells(.Cells.Count) If .Row < Me.Rows.Count Then Intersect(.Offset(1, 0).EntireRow, Me.Range("A:A")).Select End If End With Exit Sub End If End With
Feb 26 2021 09:52 AM
Feb 26 2021 10:50 AM
Als Makro kann ich Ihnen das anbieten:
sub jump() if Range ("A1"). value <> "Joe" then Range ("J1"). select end sub
I would be happy to know if I could help.
I know I don't know anything (Socrates)
* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.
Mar 02 2021 09:40 AM
After testing the spreadsheet, I decided I would like to have the cursor return to column A as you suggested. The code you provided does that, however, is it possible to have the cursor move to the target column only on the tab key, and back to column A only when I use the enter key. The way it works now, is after I enter data in my target column, whether I tab or enter, the cursor returns to column A. In some cases, I need to enter data in not only my target column, but a few following columns in that same row. I've attached a copy of the spreadsheet so you can see what I'm working with.
Mar 02 2021 12:00 PM
Thanks for uploading the workbook, that helps quite a bit.
I added some additional procedures that will reassign the Enter key behavior (both the standard enter key and the numeric keypad enter key) to run a procedure when they are pressed that will go to Column A (next row down).
The enter keys will be reassigned when
1) the workbook is opened and Sheet1 is the active sheet.
2) Sheet1 is activated (switching between worksheets).
3) the workbook is activated and Sheet1 is the active sheet (switching between workbooks).
The enter keys normal function should be restored when
1) the workbook is closed.
2) the worksheet is deactivated (switching to another worksheet).
3) the workbook is deactivated (switching to another workbook).
So, when Column D changes, it should go to the specified column and you should be able to continue to tab/arrow across the worksheet or select with the mouse. Then, hit enter and return to column A. This behavior should only apply to Sheet1 and shouldn't affect other worksheets/workbooks.