Goto Macro-when I place a number

[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

On Thu, 3 Aug 2006 17:36:02 -0700, John Calder
<[email protected]> wrote:

>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

[John Calder]

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

"Gord Dibben" wrote:

> 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
>
> On Thu, 3 Aug 2006 17:36:02 -0700, John Calder
> <[email protected]> wrote:
>
> >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
>
>