Microsoft Office Forums - View Single Post - [Solved] How to go to Nth precedents of active cell?

soroush.kalantari · 07-15-2023, 08:27 PM

Quote:

Originally Posted by p45cal

When you use DirectPrecedents one of the areas is the area you use in the Name inflation, that is the (if you're in column O):
INDEX(Sheet1!O$1:Sheet1!O$2…
rather than the single cell result of that name, O2 on its own.

You can get to it with
.NavigateArrow TowardPrecedent:=True, ArrowNumber:=5, LinkNumber:=1
but you need to work out the arrownumber and linknumber. Good luck with that!
It all gets very complicated; see:
Excel VBA code to trace precedents of cell - Stack Overflow
vba - Programmatically select other sheet precedents or dependents in Excel - Stack Overflow
When called from an Excel VBA UDF, Range.Precedents returns the range and not its precedents. Is there a workaround? - Stack Overflow

Why do you want to do this?

Thank you for your reply. The area of named range can suffice me, although I can get the single cell result by adding some new line of codes (see the modified code. I define relative named range in the way the last cell of the area is the single cell result). But a problem remains: when I am in cell E1 (column E) and I enter 1 in the inputbox, excel goes to I1: I2 range rather than e1: e2 range(which is expected according to your reply) Can you guide me on this issue?

Sub gotopresedence()

Dim rng As Range
Dim n As Integer

Application.Goto Reference:=Selection

Application.Goto Reference:=ActiveCell.DirectPrecedents.Areas(Input Box("which precedence¿"))
Set rng = Selection
n = Selection.Rows.Count
rng(n).Select
End Sub