Indirect function - relative reference

V

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
 
J

JE McGimpsey

If I understand you correctly, you're trying to jump to a cell in column
A of the sheet specified in column 8 of the active row which has the
same value as the cell in column 1 of the active row. If that's the
case, I'd suggest

Dim rFound As Range
Dim vWhat As Variant
With Worksheets(Cells(ActiveCell.Row, 8).Value)
Set rFound = .Columns(1).Find( _
What:=Cells(ActiveCell.Row, 1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
MatchCase:=False)
If Not rFound Is Nothing Then Application.Goto rFound
End With
 
V

Vladimir

I tried it and it works.

Thanks a lot
Vladimir

JE McGimpsey said:
If I understand you correctly, you're trying to jump to a cell in column
A of the sheet specified in column 8 of the active row which has the
same value as the cell in column 1 of the active row. If that's the
case, I'd suggest

Dim rFound As Range
Dim vWhat As Variant
With Worksheets(Cells(ActiveCell.Row, 8).Value)
Set rFound = .Columns(1).Find( _
What:=Cells(ActiveCell.Row, 1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
MatchCase:=False)
If Not rFound Is Nothing Then Application.Goto rFound
End With
Click to expand...
 
Top