I have written the following code to make excel go to nth direct precedents of a cell which are in the same sheet. (As far as I know, there is no way to write a code that does this job for precedents which are out of the sheet the active cell lies.) it failes to do its expected job. (see the excel attachment, when running the code for cell E1 and entering 2 in the input box appearing after running the code, it is expected to go to cell D4 but it goes to cell c2.

Sub gotopresedence()




Dim Rng As Range, RngPrecedents As Range

Dim i As Long, j As Long
Application.Goto Reference:=Selection
Set Rng = ActiveCell
Set RngPrecedents = Rng.DirectPrecedents
Application.Goto Reference:=RngPrecedents(InputBox("what presedence"))
End Sub

Attached Files

direct.precenedts.xlsm (15.7 KB, 4 views)

This should do it (no need for the other lines):It's the Areas part that was missing.


re:Have an explore of the likes of:

@ p45cal. Thank you very much. Your reply solved the problem.

@ p45cal. I have a follow up question. Is there a way to make this code do the job, when the precedence is a relative defined name? (See the excel attachment. I have defined a relative name (“inflation”) and when in cell “E1”and enter 1 in the input box, I expect to go “E2”.)

Attached Files

direct.precenedts for relativenames.xlsm (18.6 KB, 1 views)

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

Not really, the links I provided (a) could help but (b) show how complex things can get!