Goto Macro

[John Calder]

Hi

I run Win2K with Excel 2K

I require a macro that when I place a number in a cell that the curser then
jumps to a particular cell (or range name if that is the better way to do it)

ie;

Action

In cell A1 I type the number 1

Result

The curser jumps to cell A20

Action

In cell A1 I type the number 2

Result

The curser jumps to cell A21

etc etc etc

Any help is much appreciated

John Calder

 

[Gord Dibben]

John

In the sheet module....................

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
Select Case Target.Value
Case Is = 1: Range("A20").Select
Case Is = 2: Range("A21").Select
Case Is = 3: Range("A22").Select
'add the rest of the etc.'s here
End Select
CleanUp:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP

 

[John Calder]

Thanks a lot Gord. I will give it a try!

John

In the sheet module....................

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
Select Case Target.Value
Case Is = 1: Range("A20").Select
Case Is = 2: Range("A21").Select
Case Is = 3: Range("A22").Select
'add the rest of the etc.'s here
End Select
CleanUp:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP