#1
|
|||
|
|||
How to go to Nth precedents of active cell?
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 |
#2
|
||||
|
||||
This should do it (no need for the other lines):
Code:
Sub gotopresedence() Application.Goto Reference:=ActiveCell.DirectPrecedents.Areas(InputBox("what precedence")) End Sub re: Quote:
Code:
With ActiveCell .ShowPrecedents .NavigateArrow TowardPrecedent:=True, ArrowNumber:=1, LinkNumber:=1 .Parent.ClearArrows End With |
#3
|
|||
|
|||
@ p45cal. Thank you very much. Your reply solved the problem.
|
#4
|
|||
|
|||
@ 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”.)
|
#5
|
||||
|
||||
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? |
#6
|
|||
|
|||
Quote:
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 |
#7
|
||||
|
||||
Not really, the links I provided (a) could help but (b) show how complex things can get!
|
Tags |
prcedent |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
access active cell in word vba | littlepeaks | Word VBA | 5 | 08-24-2022 09:14 PM |
Move active cell down | Marcia | Excel Programming | 2 | 06-22-2022 04:10 PM |
Change active cell after running macro in active cell via selection change | Peterson | Excel Programming | 3 | 03-07-2021 12:19 PM |
Cell equal value new value of active cell which is a drop down | hassanmoukadem | Excel Programming | 1 | 04-13-2020 07:54 PM |
Clear all cell colors within a range starting at cell A8 and change row of active cell to yellow | FUGMAN | Excel Programming | 7 | 02-05-2017 08:37 AM |