Indirect function - relative reference

[Vladimir]

Hi,

I struggle to create a macro with INDIRECT function that would jump to
different cells. What cell I want to jump to depends on a currently selected
cell. This means I need to put a relative reference into the function.

Here is my function - I need to replace R1C1 expression with a realtive one
(currently selected cell address with the same behavior as R1C1 expression).
How can I do that?

Application.Goto Reference:= _

"INDIRECT(CONCATENATE(R56C8,""!"",""a"",MATCH(R56C1,INDIRECT(CONCATENATE(R56
C8,1)),false)))"

Whatever I do I get following error mesage:
Run-time error '1004'

Thanks in advance
Vladimir

 

[Tom Ogilvy]

Dim rng as Range
set rng = Range(Range("H56") & "!A" & Application.Match( _
Range("A56"),Range(Range("H56") & "!A1").EntireRow,0))
Applicaton.GoTo Reference:=rng

If H56 on the Active sheet holds sheet2
and Sheet 2 row1 has the numbers 1 to 20 in columns A to T

and A56 on the activesheet holds the number 10, then this goes to
Sheet2!A10

 

[Vladimir]

Thanks, but I might badly express myself.

What I need is to persuade the macro to read the current position of the
active cell.
So, if the active cell will be B2 the expression would be:
Application.Goto Reference:= _

"INDIRECT(CONCATENATE(R2C2,""!"",""a"",MATCH(R2C1,INDIRECT(CONCATENATE(R2C2,
1)),0)))"

if the active cell will be B10 the expression would be
Application.Goto Reference:= _

"INDIRECT(CONCATENATE(R10C2,""!"",""a"",MATCH(R10C1,INDIRECT(CONCATENATE(R10
C2,1)),0)))"

What I intend to do is to double click on a cell - trigger a macro that will
read its contents (range name) and in this range find a value from the 1st
column on the same row of the active cell and jump on a cell in this range
that contains this value.

How can I manage that?

Thanks Vladimir

 

[Tom Ogilvy]

set rng = Range(ActiveCell & "!A" & Application.Match( _
Cells(ActiveCell.row,1),Range(ActiveCell & "!A1").EntireRow,0))
Applicaton.GoTo Reference:=rng