Indirect function - relative reference

[news.microsoft.com]

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

 

[Jerry W. Lewis]

Your reply/second post of the same question made it look like your
question had already answered when it hadn't. That probably limited the
number of responses you received. In future be more patient.

Giving more detail in the original question would also be helpful. For
instance, what is contained in the referenced cells, and what to you
expect the formula to return? That would allow someone to test a
solution before proposing it to you.

Also, it is not clear whether your original formula works as is (and you
just want it converted to relative reference) or not. I suspect that it
does not work as is, since it requires that VBA evaluate worksheet
functions. VBA has its own set of functions, and does not directly
understand worksheet function. For VBA to evaluate a string expression
involving worksheet functions, you would have to wrap the expression in
the VBA Evaluate() function.

Jerry

 

[Vladimir]

My reply was a mistake, I just couldn't have took it back.

Here is more thorough explanation of the problem.

At first - following formula does work, but it naturally does still the same
thing regardless the currently selected cell. Since I'm not a programmer
(just a little one), I have simply recorded a sequence of steps in Excel and
tried to amend it in a code.

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)))"

I need to replace R10C2 part of the expression with something like
ActiveCell.Address() - but it won't work.

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