Forum Discussion
GOTO a specific cell, based on another cell's data
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
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
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