Goto Macro

[Guest]

Hi

I am running Win2000 with Excel 2000. I would like to be able to have a
macro button that takes me to a specific cell base on a certain criteria.

Example

If I type the number 1 in cell A1 the macro button (when pressed) would take
me to cell AB1.

or

If I type the number 2 in cell A1 the macro button (when pressed) would take
me to cell AB2.

etc etc etc

I am not sure, but I,m thinking that maybe it needs some sort of lookup in
the code for this to happen?

Is this possible? and if so, how?

Any help is much appreciated.

Thanks

John

 

[Trevor Shuttleworth]

John

this is a very basic version of what you need. It needs to be specified in
the Worksheet Class module for the sheet you want to use.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
Range("AB" & Target.Value).Select
End Sub

Right click on the sheet tab, select View Code and copy and paste the code.

You might want to add code to ensure that the data entered into cell A1 is
valid, that is, numeric between 1 and 65536.

Regards

Trevor

 

[Dave Peterson]

with activesheet
.cells(.range("a1").value,"AB").select
end with

But this doesn't check to see if you typed in a valid row in A1.

 

[Pete_UK]

Why not use a hyperlink? Use cell A1 in "Sheet1" to enter 1 or 2 as you
suggest. In cell A2 enter this formula:

=HYPERLINK("#Sheet1!AB"&A1,"jump")

this will display the text "jump" in cell A2 and will be coloured blue
and underlined. Enter your (row) number in A1 and click A2 and you will
jump to cell AB_row_number.

Hope this helps.

Pete

 

[Guest]

Thanks Trevor

What you gave me works fine, but unfortunately I made a slight error in my
request.

What I need is when I enter 1 in cell A1 that it jumps to cell F2 but if I
enter 2 in cell A1 it jumps to cell H2 and if I enter 3 in cell A1 it jumps
to J2 etc etc.

As you can see the jump is to every 2nd column.

I apologise for my initial mis-information.

Thanks

 

[Pete_UK]

As an alternative, the following will give you this functionality but
without vba:

=IF(OR(A1<1,A1>20),"",HYPERLINK("#Sheet1!"&CHAR(68+2*A1)&"2","jump"))

It checks for A1 being between 1 and 20 inclusive, and when you click
"jump" it will take you to F2, H2, J2 etc in "Sheet1", as you request.
You can enter the formula in any conveniently-placed cell on any sheet
in the workbook.

Hope this helps.

Pete