Forum Discussion
GOTO a specific cell, based on another cell's data
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.
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!!!
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
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
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 Ifto:
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.
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.
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.
Als Makro kann ich Ihnen das anbieten:
sub jump() if Range ("A1"). value <> "Joe" then Range ("J1"). select end subI 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.
