GOTO a specific cell, based on another cell's data

Brass Contributor

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.

11 Replies

@Sue_G 

 

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

 

This works great! Thank you so very much!!!

@Sue_G 

 

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

 

to:

     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
Thank you! I'll make a note of this as I'm not sure this is what I want for this spreadsheet. I wanted the cursor to move to a specific column to enter data, but I have more data to enter in other columns. I just wanted the cursor to go to the first column for that particular name.

@Sue_G 

 

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.

 

Nikolino

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.

@JMB17 

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.

 

@Sue_G 

 

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.

 

You are the best! Thank you so very much...it works like a charm!!!

@JMB17 

 

hello,

 

i have a question in column B where i use data validation rules to get the user answers YES or NO. When the user answers YES then he needs to go to cell C on the same row, if he answers NO he has to go to cell D to provide more data. I tried the following code based on your example above but does not work. It always produces the error message below. What am I doing wrong? Is there a simpler way to do the same thing? THANK YOU

 

MTSILIRA_0-1657722715797.png

 

 

The code i used is :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim destCol As Range

On Error GoTo ErrHandler

If Intersect(Target.Cells(1), Me.Range("B:B")) Is Nothing Then
Exit Sub
End If

Select Case LCase(Target.Cells(1).Value)
Case vbNullString: Exit Sub
Case "YES": Set destCol = Me.Range("C:C")
Case "NO": Set destCol = Me.Range("D:D")

'// 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:="NOT VALID ANSWER."

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

@MTSILIRA 

 

I believe all you need to do is change "YES" and "NO" to lower case. No matter what case the user inputs, the lcase function will force it to lower case for purposes of the select/case statement.

 

Case "yes": Set destCol = Me.Range("C:C")
Case "no": Set destCol = Me.Range("D:D")

YES, it worked, thank you very much